Archive for the ‘ Conferences ’ Category

Speaking

This past weekend, I had the most challenging experience I’ve ever encountered as it relates to speaking in public.

I’ve been blessed with opportunities to speak at Dev Connections, SQL Saturday events, User Group events and of course, in my day to day life as a consultant I speak to differing audiences on a fairly regular basis.

I’ve read a lot over the years as it pertains to giving a good presentation, I’ve seen many good public speakers and some real duds as well.

However, I now have a new found appreciation for folks who speak to audiences on manners that aren’t technical or in general, targeted topics / concepts.

This past weekend I gave a eulogy.

The individual I gave it for was close to me, however, even if they hadn’t been, I believe that the same challenges would have existed.

It’s interesting – one of the first rules in public speaking is to “Know Thy Audience” – this is, of course critically important to being an effective speaker, consultant, employee, manager, CxO, etc…
So, what is the audience for a eulogy?

Well, it’s obvious, right?

Clearly, it’s full of people who knew the deceased – but then there are some wrinkles in the midst of that – differing relationships – from Mother, Daughter, Wife, Husband, Son, Father, Brother, Sister, 1st cousin, 8th cousin, grandkids, friends, colleagues, etc… and then in the midst of that wrinkle there are some very significant generational gaps. From toddlers to seniors who were born during the great depression.

Wow. Imagine preparing your favorite technical session with that audience in mind – and then, do it without a slide deck. Do it when EVERYONE is watching YOU. Do it when you see tears streaming down a cheek. Do it when there are tears streaming down your cheek and the only thing you can feel is a lump in your throat the size of a grapefruit.

The purpose of this post is to challenge you, the reader, to get up – be bold. Speak. It’s to challenge you in your personal life. Take that risk. I don’t know what it is, but take it. There is but this moment in life, seize it.
It’s tough, I’m not going to lie. However, it does get easier. You will get better. You’ve heard the old adage a thousand times “Practice makes perfect” – while I’m not sure about the “perfection” aspect of it, it certainly makes one better, a lot better.

Had I not faced my fear of speaking in front of my peers on technically related items, I’m not sure how the Eulogy would have turned out – and let’s face it, what’s more important in life, providing a good technical session or being able to prepare oneself for a life event that takes on a very real sense of importance?

I’m not trying to diminish or otherwise negate the importance of technical sessions – they have provided me with a great deal of knowledge over the years and I have certainly benefitted from them, however, having attended many of them and given more than a handful, it’s my experience that, having given a eulogy, my approach to giving technical sessions is going to change – perhaps drastically.

First and foremost, preparation.

In the normal course of giving a technical presentation, I will typically come up with what I think is a good idea. Then I’ll scribe an outline, put together the demo’s I believe illustrate the points / relevant technical details and then create some slides, add the detail to my outline and practice.

In the course of giving the eulogy – it was a very different experience. First, I ASKED many people what they thought of different ideas, I bounced around different stories, I THOUGHT a lot about the audience. I wrote some notes, scribbled down thoughts over the course of a day or two, then sat down and wrote it out.

Then I practiced. I practiced in a way I hadn’t before. I practiced speaking the eulogy out loud with my favorite music playing. I practiced it while driving the car. I practiced it walking around. I practiced it while hitting golf balls.
This was different for me – it had been my intent to practice in enough “distracted” environments that when it came time to do it “live”, I’d be ready for the grapefruit in my throat, I’d be ready for the faces in the audience who were looking at me.
And you know what? It worked. Was my delivery flawless? No. Did I recite the eulogy from memory without once checking my notes? No. Did I have some unexpected hiccups? Yup. Did they throw me? Nope.
Was I able to connect with every person in the Church? I haven’t a clue – but I do know that out of the ~ 175 folks in attendance, I was blessed to hear many of them say “thank you, it was so eloquent, etc…” Afterwards, I asked a couple of different generational groups what they heard me say – and guess what? They heard the same thing. Victory. In a big, huge way.

How many times have you walked out of a technical presentation with a different set of “facts” or even “ideas” about the subject matter than the guy who was sitting next to you? I can answer that for you, it happens very often.

Now, I’ll likely take some flak for this, and all I can say is “bring it on” – here you are: if your session has folks walking out with differing ideas of what you just spoke about, you weren’t as effective as you’d hoped. Yup, that’s right, as a speaker, you failed them, and you failed yourself.

I’m guilty of this. I’m absolutely certain that there’s been ambiguity in my sessions. I always chalked it up to “different people have different levels of understanding”, so, it’s not on me.

But guess what? It is.

Here’s the thing – when I give presentations to my clients, I can often anticipate the outcome of the conversation. If I cannot anticipate the questions which will come my way, based on information which I’ve delivered, then either I don’t know my subject area well enough or I haven’t done enough due diligence. If the “takeaways” from a client presentation are not 100 percent clear to the client and myself, I’ve failed us both and my time as being their consultant will surely come to an end at some point down the road.

This is where I see this fork in the road. Giving a presentation to a client or giving a eulogy both have different levels of meaning and importance. Giving a session at a conference doesn’t have the same types of pressure. There are different pressures with technical conferences – there’s the being grilled / vetted by your peers if you are wrong. There’s absolutely accountability from an accuracy of the presentation standpoint.

BUT,

There’s NOT any accountability from the perspective of WHAT you conveyed to your audience in terms of the HOW’s and WHEN’s and WHY’s.

This is really important and I’m *finally* getting to the heart of this post.

If you are going to give a presentation or if you are going to attend a presentation and the speaker makes a statement such as “this technology will save your DBA staff so much time” or “this new feature will help increase your performance” – stop. Full stop. Are you sure? How sure are you? I’ll answer this for you, you aren’t. Neither the speaker, nor the attendee.

However, as the speaker, you can be sure. As the attendee, it’s your responsibility to be sure.

It’s always amazed me how often I’ll give a presentation and the amount of questions at the end aren’t more numerous or specific with implementation details or their specific environment or challenge.

Know Thy Audience pertains to the speaker. Know Thy Self pertains to the attendee and Mr. / Ms. Attendee, if you don’t know the subject material being presented on then please do one of two things, either learn it before you attend the session or spend a significant amount of time after the session learning WHEN and WHY it *might* be a good idea to implement.

I cannot count, on both hands or feet, how many times I’ve seen terrific technology used in the wrong manner, resulting in botched software, poor quality, high TCO, headaches, nightmares, well, you get the idea.

This also comes back to an old post on technical debt I wrote once. It feeds into it perfectly and, for those of you who speak, you have a larger responsibility, and for those attendees, you also have a larger responsibility. Don’t feed the monster which is technical debt. While we’ve all done it, most often out of the best of intentions, there exists an opportunity to acknowledge the reality and learn from it.

So, here’s the challenge to the speakers out there – focus less on breadth and depth of a topic and focus more on the relevance of a topic. Attendees, do yourself and your respective companies a huge service and ensure that you are ready for the material which is about to be presented.

Plan Guides

In November 2008 I had the opportunity to speak at Dev Connections.  One of the subjects that I covered was Plan Guides.  The abstract read “In SQL Server 2008, there exists a way to change or create a new plan for a query without touching the query itself. In environments where the database was provided by a third-party or in a high availability environment, these will help to decrease your impact on production systems while improving performance at the same time. Over the course of the session, we will walk through the creation of a plan guide.“ 

The reason for this blog post is that I was discussing the upcoming event that the Denver SQLUG is hosting with Kalen Delaney on June 9th, 2010.  Register here

I’ve already registered and I’m very much looking forward to hearing her talk on this subject.  I wanted to provide some background and familiarity with to plan guides for those who follow my blog and will be attending the UG meeting on the 9th.  

For me personally, understanding plan guides, why they matter, how to use them and what it’s all about stems from a solid understanding of execution plans and how SQL Server chooses to get to the data based on the query that you are passing it.  This is not a trivial subject and one that is best understood with a solid foundation of HOW the optimizer reports the methods it is utilizing in the retrieving of data. 

In my slide deck these questions are answered:  

     Q:  Why Plan Guides?  

     A:  Due to increased complexity in database environments today.  These complexities include Linq to SQL and it’s impact on plan cache.  C.O.T.S (Commercial Off the Shelf) implementations and the ability to be Proactive vs. Reactive in your environments. 

     Q:  What are they?  (Plan Guides) 

     A:  Plan Guides can be used to optimize the performance of queries when you cannot or do not want to change the text of the query directly.  Plan Guides can be useful when a subset of queries in a database application deployed from a third-party vendor are not performing as expected and the ability to change the query does not exist.  Plan Guides influence optimization of queries by attaching query hints or a fixed query plan to them.  (The last answer is from Microsoft). 

The slide deck and the demo also talk about the different memory pools in SQL Server and how to “see” the cache on the server.  I also touch on the life of cache and why it’s relevant. 

One of the biggest items in this presentation revolved around parameterization (forced or simple).  This is an incredibly powerful option that must be tested thoroughly in your environment.  The gist of it is that if you have a ton of compilations in your environment the changing of parameterizataion to forced might be something to really look into as it will increase your concurrency due to the decrease in compilations. 

Up until now in the demonstration, all I had covered was background information that is helpful to truly grasp Plan Guides… the last half of the presentation is on plan guides, how to create them, disable them, view them etc…    

Without further ado, here is the Powerpoint and the set of t-sql scripts that I used in my demo’s.  I’ve also commented the code that you can find below as it should help you when you walk through the powerpoint presentation. 

We begin with an Object Plan Guide   


USE AdventureWorks2008; 

go 

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60)) 

AS 

BEGIN 

    SELECT * 

    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 

        Sales.SalesTerritory AS t 

    WHERE h.CustomerID = c.CustomerID 

        AND c.TerritoryID = t.TerritoryID 

        AND CountryRegionCode = @Country_region 

END;        

GO 

  

– Create an OBJECT plan guide 

 

sp_create_plan_guide 

@name = N'Guide1', 

@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,       

        Sales.Customer AS c, 

        Sales.SalesTerritory AS t 

        WHERE h.CustomerID = c.CustomerID 

            AND c.TerritoryID = t.TerritoryID 

            AND CountryRegionCode = @Country_region', 

@type = N'OBJECT', 

@module_or_batch = N'Sales.GetSalesOrderByCountry', 

@params = NULL, 

@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'  

   

– @hints || base this on selectivity of the data “US” is good because it will be representative of rougly 2/3rds 

–                           of the depth in the index / heap 

—  Where do the created plans exist? 

 

select * from sys.plan_guides  -- (notice the HINTS column) 

GO 

--Disable the plan guide. 

EXEC sp_control_plan_guide N'DISABLE', N'Guide1'; 

GO 

select * from sys.plan_guides 

-- Now shows as disabled 

--Enable the plan guide. 

EXEC sp_control_plan_guide N'ENABLE', N'Guide1'; 

GO 

select * from sys.plan_guides 

-- Now shows as enabled 

--Drop the plan guide. 

EXEC sp_control_plan_guide N'DROP', N'Guide1'; 

GO 

select * from sys.plan_guides 

-- Now it's gone 

-- Disable ALL plan guides. 

EXEC sp_control_plan_guide N'DISABLE ALL'; 

GO 

   

– SQL Plan Guide 

– Example:  Set the Maxdop (Degree of parallelism = 1 

 

EXEC sp_create_plan_guide 

    @name = N'Guide1', 

    @stmt = N'SELECT TOP 1 * 

              FROM Sales.SalesOrderHeader 

              ORDER BY OrderDate DESC', 

    @type = N'SQL', 

    @module_or_batch = NULL, 

    @params = NULL, 

    @hints = N'OPTION (MAXDOP 1)'; 

GO 

   

 – NOTE ** any Option clause in a select statement will work with the @hints parameter. 

– Template Plan Guide 

          — Let’s Force the parameterization on this statement.  

 

DECLARE @stmt nvarchar(max); 

DECLARE @params nvarchar(max); 

EXEC sp_get_query_template 

    N'SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader AS h 

      INNER JOIN AdventureWorks2008.Sales.SalesOrderDetail AS d 

          ON h.SalesOrderID = d.SalesOrderID 

      WHERE h.SalesOrderID = 45639;', 

    @stmt OUTPUT, 

    @params OUTPUT 

EXEC sp_create_plan_guide N'TemplateGuide1', 

    @stmt, 

    N'TEMPLATE', 

    NULL, 

    @params, 

    N'OPTION(PARAMETERIZATION FORCED)'; 

GO 

   

  — NOTE:  This forces the query to be parameterized and then a guide can be created against it. 

  — Creating a plan using XML_showplan 

 

USE AdventureWorks2008; 

GO 

SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; 

GO 

DECLARE @xml_showplan nvarchar(max); 

SET @xml_showplan = (SELECT query_plan 

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 

    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp 

    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%'); 

EXEC sp_create_plan_guide 

    @name = N'Guide1_from_XML_showplan', 

    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 

    @type = N'SQL', 

    @module_or_batch = NULL, 

    @params = NULL, 

    @hints =@xml_showplan; 

GO 

   

– Create a plan guide for the query by specifying the query plan in the plan cache via the plan handle.  

 

USE AdventureWorks2008; 

GO 

SELECT WorkOrderID, p.Name, OrderQty, DueDate 

FROM Production.WorkOrder AS w 

JOIN Production.Product AS p ON w.ProductID = p.ProductID 

WHERE p.ProductSubcategoryID > 4 

ORDER BY p.Name, DueDate; 

GO                           

   

– Inspect the query plan by using dynamic management views. 

 

SELECT * FROM sys.dm_exec_query_stats AS qs 

CROSS APPLY sys.dm_exec_sql_text(sql_handle) 

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp 

WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%'; 

GO 

   

  — Create a plan guide for the query by specifying the query plan in the plan cache via the plan handle. 

 

DECLARE @plan_handle varbinary(64); 

DECLARE @offset int; 

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset 

FROM sys.dm_exec_query_stats AS qs 

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st 

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp 

WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%'; 

EXECUTE sp_create_plan_guide_from_handle 

    @name =  N'Guide8', 

    @plan_handle = @plan_handle, 

    @statement_start_offset = @offset; 

GO 

   

Reference the previous example of ….. “Means that there are 4811 plans for three nearly identical statements” 

With this, a plan can be created against that string….   ‘SELECT WorkOrderID, p.Name, OrderQty, DueDate%’ 

This is HUGE!!!  Think about the Commercial-Off-The-Shelf (COTS) implementations in your environment where you are largely bound by licensing and cannot physcially modify the schema, triggers, clustered indexes etc…  Additionally, there are still many applications that use a lot of dynamically built or in-line t-sql that don’t always perform well or, in the case of the example that I cited, there are hundreds or thousands of execution plans for the same t-sql statement. 

This next piece of t-sql checks the validity of the plan guides in the system. 

             An empty result set means that all plan guides are valid. 

 

USE AdventureWorks2008; 

GO 

SELECT plan_guide_id, msgnum, severity, state, message 

FROM sys.plan_guides 

CROSS APPLY fn_validate_plan_guide(plan_guide_id); 

GO 

   

Select statement taken from SQL Profiler 

Look at the execution plan. 

 

 SELECT COUNT(*) AS c 

FROM Sales.SalesOrderHeader AS h 

INNER JOIN Sales.SalesOrderDetail AS d 

  ON h.SalesOrderID = d.SalesOrderID 

WHERE h.OrderDate BETWEEN '20000101' and '20050101'; 

GO 

   

Look at the execution plan and note the Hash Match Inner Join  

Now, let’s make it a Merge Join instead. 

 

EXEC sp_create_plan_guide 

    @name = N'MyGuideProfiler1', 

    @stmt = N' SELECT COUNT(*) AS c 

FROM Sales.SalesOrderHeader AS h 

INNER JOIN Sales.SalesOrderDetail AS d 

  ON h.SalesOrderID = d.SalesOrderID 

WHERE h.OrderDate BETWEEN ''20000101'' and ''20050101''; 

', 

    @type = N'SQL', 

    @module_or_batch = NULL, 

    @params = NULL, 

    @hints = N'OPTION (MERGE JOIN)'; 

   

Spacing is critical on this. 

Check the statement to ensure that the planguide will force a merge join 

 

 SELECT COUNT(*) AS c 

FROM Sales.SalesOrderHeader AS h 

INNER JOIN Sales.SalesOrderDetail AS d 

  ON h.SalesOrderID = d.SalesOrderID 

WHERE h.OrderDate BETWEEN '20000101' and '20050101'; 

--Drop the plan guide. (if needed) 

EXEC sp_control_plan_guide N'DROP', N'MyGuideProfiler1'; 

EXEC sp_control_plan_guide N'DROP', N'TemplateGuide1'; 

EXEC sp_control_plan_guide N'DROP', N'Guide1'; 

    

– Jeremy Lowell http://www.datarealized.com || http://datarealized.wordpress.com || http://sqlperspectives.wordpress.com 

Feel free to ask questions as you step through this and I look forward to seeing everyone on the 9th!

DMV's and t-sql for the DBA

The Rocky Mountain Tri-Fecta was today and I had the opportunity to speak.  I mentioned to those brave souls who attended my session that I would post the slide deck and the demo’s.

Feel free to download them (links are at the end of this post) and provide your comments / thoughts.

I had a great time attending the event and I learned a great number of things.  A special thanks to everyone involved with putting this together.  I’m sill amazed and the stacks of empty pizza boxes.  WOW!

Part of this post is also to ask the readers of this blog if they would be interested in checking out a new tool that I’ve been testing over the last couple of months.  It’s formally known as SmartMon and it provides a form of pattern analysis against the data looking for anomalies and patterns of similar behavior.  It has decreased the amount of time that I spend doing analysis on perfmon files by many factors.  It’s a great tool and the guys building it are stand up guys in the San Jose, CA area.  If you are interested in becoming one of the first handful of people to see the release of this product, e-mail feedback@perfmonanalysis.com.  Please mention that I referred you.

Without further ado, enjoy the SQL and thanks again for the opportunity to share what I’ve learned with all of you.

RockyMountainTriFecta (Link to the slide deck PPT)

Essentials-DMVs_tsql_dba (t-sql – Demo)

Policy Management

In Novemeber, I had the opportunity to present at DevConnections.  I spoke on two topics, Policy Management and Plan Guides.  Both of these are new / unique to SQL Server 2008 and I really love both of the additions to the SQL Server platform.  

In this blog post I have attached two files; one of them is my powerpoint slide deck and the other is the script I used for the demo’s.  

This was a very difficult talk to give as the concept is/was relatively new and the amount of information available was scarce when I was developing the content.   

So, what is Policy Management?  Microsoft’s definition is as follows :  “Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008.  Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.”  What this definition leaves out is something of great importance.  This new feature CAN be used with down versions, providing that the facet you want to throw a policy around is availalable in that version.  Meaning, if you want to monitor datafiles or schema changes in a SQL Server 2005 instance, you can!  

I found there to be three components to Policy Management.  

  1. Policy Management  — Creation of conditions and policies
  2. Explicit Administration — One to many managed targets
  3. Automated Administration — Automate enforcement of policies

For each of these there are components – Refer to the slide deck and Microsoft’s site for more information.  

One of the biggest helps that I had when leaning Policy Management was the following CTE:   

The results of this query provide all of the available facet’s and which mode then can be run for.  This is a huge help as going through the gui to see if something is availalble for a certain type of mode can take a while.   

USE MSDB;
with automatedpolicyexecutionmode (modeid, modename)
as
          (select * from (values (0, ‘On Demand’), 
                              (1,’Enforce Compliance’), 
                              (2, ‘Check on Change and Log’),
                              (4, ‘Check on Schedule and Log’))
as em(modeid, modename))
   

So far I have successfully implemented policies against a host of the facets offered.  Microsoft is going to continue to build on this inital release as time goes on; so look to new additions of facets as time goes on.  

Enjoy Policy Based Management and may it save you a great deal of time, decrease your daily / weekly checklist of items and enable you to provide further value to your organization.  

SELECT dmf.management_facet_id as facetid, dmf.name as facetname, apemode.modename
FROM syspolicy_management_facets as dmf
       INNER JOIN automatedpolicyexecutionmode as apemode on dmf.execution_mode & apemode.modeid=apemode.modeid
ORDER BY dmf.name, apemode.modename  

If you walk through the slide deck and the demo script; you can quickly see how easy it is to create a policy.  Once you have one created (IN DEVELOPMENT),  =), then evaluate the policy in two ways.  The first way you will want to ensure that the evaluation is successful.  The second time, break it, so that the evaluation of the policy is false.  Once you have done this, run the query below.  

 USE MSDB;
GO
SELECT a.execution_date AS 'Date Run' , 
                  c.name AS 'Policy' , 
                  a.target_query_expression AS 'Policy Failure Targets' , 
                  d.name as 'Condition',
                  d.description as 'Condition Description', 
                  d.facet as 'Condition Facet'
FROM syspolicy_policy_execution_history_details_internal a
              INNER JOIN syspolicy_policy_execution_history_internal b ON a.history_id = b.history_id
              INNER JOIN syspolicy_policies_internal c ON b.policy_id = c.policy_id
                           INNER JOIN syspolicy_conditions d ON c.condition_id = d.condition_id
WHERE a.result = 0
ORDER BY a.execution_date DESC, c.name ASC, d.name ASC 

Attachements for this post ::  

Script: policymanagementscript2  — The majority of the t-sql in this document can be found below.  

Slide Deck: sql-connections-conference-slides-policy-management2  


USE msdb;
GO
WITH automatedpolicyexecutionmode (modeid, modename)
AS
 (select * from (values  (0, 'On Demand'),
    (1,'Enforce Compliance'),
    (2, 'Check on Change and Log'),
    (4, 'Check on Schedule and Log'))
          as em(modeid, modename))
     SELECT dmf.management_facet_id as facetid, dmf.name as facetname, apemode.modename
     FROM syspolicy_management_facets as dmf
        INNER JOIN automatedpolicyexecutionmode as apemode on dmf.execution_mode & apemode.modeid=apemode.modeid
     OREDER BY dmf.name, apemode.modename

– Demo 1
– Greenfield development
– Standard Naming convention
– Create Conditions
– Create Policy 


use DemoDB
go

-- Successful Object Creation
-- Table
CREATE TABLE UserTable_test_me3 (test int)
GO

-- Procedure
CREATE Procedure PRC_Test_proc AS SELECT GETDATE()
GO

-- View
CREATE VIEW UserView_Test AS SELECT test from UserTable_test_me3
GO

-- Failed Object Creation due to Policy enforcement
-- Table
CREATE TABLE test_me3 (test int)
GO

Create Procedure Test_proc AS select GETDATE()
GO

Create View VW_Test as select test from UserTable_test_me3
GO

Create View Test as select test from UserTable_test_me3
GO

-- Fails
EXEC sp_rename 'UserTable_test_me3', 'test_me3'
GO 

--  Check MSDB for policy execution Failures

USE msdb;
GO

     SELECT a.execution_date AS 'Date Run' , c.name AS 'Policy', a.target_query_expression AS 'Policy Failure Targets',
                     D.name as 'Condition', D.description as 'Condition Description', D.facet as 'Condition Facet'
      FROM syspolicy_policy_execution_history_details_internal a
        INNER JOIN syspolicy_policy_execution_history_internal b ON a.history_id = b.history_id
        INNER JOIN  syspolicy_policies_internal c  ON b.policy_id = c.policy_id
        INNER JOIN syspolicy_conditions d  ON c.condition_id = d.condition_id
    WHERE a.result = 0 AND a.execution_date >= GETDATE() -1
    ORDER BY a.execution_date DESC, c.name ASC, a.target_query_expression ASC;
GO

-- Existing environment somewhat different ::

select * from msdb.dbo.syspolicy_policies_internal order by date_created desc;
GO
-- Delete the policy
 -- Can use SSMS to do this as well.
Declare @Object_set_ID_Delete INT
set @Object_set_ID_Delete = (select object_set_id from msdb.dbo.syspolicy_policies_internal where name = 'Standard Naming Convention')

EXEC msdb.dbo.sp_syspolicy_delete_policy @name= 'Standard Naming Convention'
EXEC msdb.dbo.sp_syspolicy_delete_object_set @object_set_id = @Object_set_ID_Delete
GO
-- Delete the Condition
 -- Can use SSMS to do this as well.
 EXEC msdb.dbo.sp_syspolicy_delete_condition @name = 'Standard - Naming Convention'

-- Create an Existing environment

Create Database Demo_existing
GO

USE Demo_existing
GO
create table test_me3 (test int)
GO

Create Procedure Test_proc AS select GETDATE() as The_Time_is_Now
GO

Create View VW_Test as select test from test_me3
GO

Create View Test as select test from test_me3
GO

–  Re-Create Condition and Policy
– Using t-sql versus SSMS
  

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Standard - Naming Convention', @description=N'Enforcement of Standard Naming Convention

The Object you attempted to create was rolled back.

This Policy prevents the following naming standards ::

Object names MAY NOT begin with "tbl" OR "sp" OR "vw"

AND

Ojbect Names MUST begin with "UserTable" OR "PRC" OR "UserView"

', @facet=N'IMultipartNameFacet', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>AND</OpType>
  <Count>2</Count>
  <Group>
    <TypeClass>Bool</TypeClass>
    <Count>1</Count>
    <Operator>
      <TypeClass>Bool</TypeClass>
      <OpType>OR</OpType>
      <Count>2</Count>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>OR</OpType>
        <Count>2</Count>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>NOT_LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>tbl%</Value>
          </Constant>
        </Operator>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>NOT_LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>vw%</Value>
          </Constant>
        </Operator>
      </Operator>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>NOT_LIKE</OpType>
        <Count>2</Count>
        <Attribute>
          <TypeClass>String</TypeClass>
          <Name>Name</Name>
        </Attribute>
        <Constant>
          <TypeClass>String</TypeClass>
          <ObjType>System.String</ObjType>
          <Value>sp%</Value>
        </Constant>
      </Operator>
    </Operator>
  </Group>
  <Group>
    <TypeClass>Bool</TypeClass>
    <Count>1</Count>
    <Operator>
      <TypeClass>Bool</TypeClass>
      <OpType>OR</OpType>
      <Count>2</Count>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>OR</OpType>
        <Count>2</Count>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>UserTable%</Value>
          </Constant>
        </Operator>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>UserView%</Value>
          </Constant>
        </Operator>
      </Operator>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>LIKE</OpType>
        <Count>2</Count>
        <Attribute>
          <TypeClass>String</TypeClass>
          <Name>Name</Name>
        </Attribute>
        <Constant>
          <TypeClass>String</TypeClass>
          <ObjType>System.String</ObjType>
          <Value>PRC%</Value>
        </Constant>
      </Operator>
    </Operator>
  </Group>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO 

—-
–   Condition was created with an ID of XX.
—- 

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @facet=N'IMultipartNameFacet', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/StoredProcedure', @level_name=N'StoredProcedure', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/Synonym', @type=N'SYNONYM', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Synonym', @level_name=N'Synonym', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/Table', @type=N'TABLE', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/UserDefinedFunction', @type=N'FUNCTION', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/UserDefinedFunction', @level_name=N'UserDefinedFunction', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/UserDefinedType', @type=N'TYPE', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/UserDefinedType', @level_name=N'UserDefinedType', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/View', @type=N'VIEW', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/View', @level_name=N'View', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/XmlSchemaCollection', @type=N'XMLSCHEMACOLLECTION', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/XmlSchemaCollection', @level_name=N'XmlSchemaCollection', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Standard Naming Convention', @condition_name=N'Standard - Naming Convention', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @object_set=N'Standard Naming Convention_ObjectSet_1'
Select @policy_id

-- The next piece will Update the Targets for the policy
GO
EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=128, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=128, @type_skeleton=N'Server/Database/StoredProcedure', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=128, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=129, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=129, @type_skeleton=N'Server/Database/Synonym', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=129, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=130, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=130, @type_skeleton=N'Server/Database/Table', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=130, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=131, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=131, @type_skeleton=N'Server/Database/UserDefinedFunction', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=131, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=132, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=132, @type_skeleton=N'Server/Database/UserDefinedType', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=132, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=133, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=133, @type_skeleton=N'Server/Database/View', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=133, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=134, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=134, @type_skeleton=N'Server/Database/XmlSchemaCollection', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=134, @type_skeleton=N'Server/Database', @condition_name=N''
GO
EXEC msdb.dbo.sp_syspolicy_update_policy @policy_id=62, @execution_mode=1, @schedule_uid=N'00000000-0000-0000-0000-000000000000'
GO

–Verify that the new policy is working as intended 

-- Will FAIL 
<pre>create table TEST_Post_NewPolicy (testcol int)
GO

-- Will suceed
create table UserTable_Post_NewPolicy (testcol int)
GO
-- Find an object to alter
Select * from sysobjects where uid = 1 and xtype in ('v','u','p')

-- Alter object to another invalid naming convention

-- Will FAIL
Alter Procedure Test_proc
AS select GETDATE()  as [When]

--Will Suceed
Drop Procedure Test_proc

-- Alter an Invalid object to a valid naming convention

-- Successful
EXEC sp_rename 'Test', 'UserView_Test'
GO 

-- Check the execution history

USE msdb;
GO

     SELECT   a.execution_date AS 'Date Run' , c.name AS 'Policy', a.target_query_expression AS 'Policy Failure Targets',
                  D.name as 'Condition', D.description as 'Condition Description', D.facet as 'Condition Facet'
     FROM syspolicy_policy_execution_history_details_internal a
        INNER JOIN syspolicy_policy_execution_history_internal b
           ON a.history_id = b.history_id
        INNER JOIN  syspolicy_policies_internal c
         ON b.policy_id = c.policy_id
       INNER JOIN syspolicy_conditions d
        ON c.condition_id = d.condition_id
     WHERE a.result = 0 AND a.execution_date >= GETDATE() -1
     ORDER BY a.execution_date DESC, c.name ASC, a.target_query_expression ASC;
GO

With the sql above and the slide deck that is attached to this post, you should be well on your way to creating your own Policies.

vConference – This week!

This is the week that the vConference will air.  SQL Server WorldWide Users Group (SSWUG) puts on the event and this year I was chosen to speak at the event.

If you have not already registered, you can do so here : https://www.vconferenceonline.com/upcoming.asp 

I have a discount code for you to use as well : SVPJLOSP09

The recording of the event was very different from an in person conference.  It took me a few minutes to get used to the cameras and not have feedback from an audience.  That said, I’m happy with my presentations even though I moved a bit too fast and I felt that I didn’t focus on some of the points and bring them back around the way I was hoping for. 

At any rate, enjoy the conference, there is no better value for the money! 

Later this week I’ll have a couple of posts up about my sessions at Dev Connections.  They will get a lot more technical and it’s my hope, be of use for you.