Feb 16, 2015
Dear Diary,
A few weeks back I introduced HAL002 to a database managed by some annoying devs. They were not just putting NOLOCK on almost every query but they had SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of the stored procedures. HAL’s done his job pretty well. The number of NOLOCKs and READ UNCOMMITTEDs are going down in that database. It’s not enough though. I have hundreds of servers each with dozens to hundreds of user databases. I need something that I can push out easily to handle it all.

Feb 23, 2015
Dear Diary,
After some reading I have found the solution. I’m going to use Policy Based Management! PBM (as it’s also known) allows me to create a set of rules and then using a Central Management Server (CMS) I can push each of these Policies out to any or all of the other instances. Each Policy has to have one and only one Condition. Each of those Conditions is tied to a facet. A facet is basically an object that has properties that can be tested. So because a Policy can in the end only be tied to a single facet I will have to have a different policy/condition each for SPs, UDFs, Views and Triggers.

By preference I want my policies to use the Evaluation Mode On Change: prevent but the facets for Views and Triggers don’t appear to allow that. So instead I’m using the Evaluation Mode Schedule on them with a schedule for each night. Then I’ll evaluate the results in the morning and “fix” them. There are two other options for Evaluation Mode. On Change: log only that I don’t really need and On Demand. On Demand is one I’ll probably be using at least occasionally. All that really means is that I can evaluate the policy (or policies) when I want to.

On and in case I need it latter here is the script to generate these Conditions and Policies.

----------------------------------------------------------------------------------
-- Condition: No NOLOCK in Stored Procedure
----------------------------------------------------------------------------------
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'No NOLOCK in Stored Procedures', @description=N'', @facet=N'StoredProcedure', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>AND</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>MethodName</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%NOLOCK%</Value>
    </Constant>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>MethodName</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%UNCOMMITTED%</Value>
    </Constant>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO

---------------------------------------------------------------------------------
-- Policy: No NOLOCK in SPs
---------------------------------------------------------------------------------
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'No NOLOCK in SPs_ObjectSet', @facet=N'StoredProcedure', @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'No NOLOCK in SPs_ObjectSet', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=True, @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
GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'No NOLOCK in SPs', @condition_name=N'No NOLOCK in Stored Procedures', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @object_set=N'No NOLOCK in SPs_ObjectSet'
Select @policy_id
GO

----------------------------------------------------------------------------------
-- Condition: No NOLOCK in Triggers
----------------------------------------------------------------------------------
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'No NOLOCK in Triggers', @description=N'', @facet=N'Trigger', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>AND</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>MethodName</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%NOLOCK%</Value>
    </Constant>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>MethodName</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%UNCOMMITTED%</Value>
    </Constant>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO

---------------------------------------------------------------------------------
-- Policy: No NOLOCK in Triggers
---------------------------------------------------------------------------------
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'No NOLOCK in Triggers_ObjectSet', @facet=N'Trigger', @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'No NOLOCK in Triggers_ObjectSet', @type_skeleton=N'Server/Database/Table/Trigger', @type=N'TRIGGER', @enabled=True, @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/Trigger', @level_name=N'Trigger', @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/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'No NOLOCK in Triggers_ObjectSet', @type_skeleton=N'Server/Database/View/Trigger', @type=N'TRIGGER', @enabled=True, @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/Trigger', @level_name=N'Trigger', @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/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
GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'No NOLOCK in Triggers', @condition_name=N'No NOLOCK in Triggers', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', 
     @schedule_uid=N'770725f7-3817-4187-a041-6fc1f846bbc4', @execution_mode=4, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'No NOLOCK in Triggers_ObjectSet'
Select @policy_id
GO

----------------------------------------------------------------------------------
-- Condition: No NOLOCK in User Defined Functions
----------------------------------------------------------------------------------
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'No NOLOCK in User Defined Functions', @description=N'', @facet=N'UserDefinedFunction', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>AND</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>MethodName</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%NOLOCK%</Value>
    </Constant>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>MethodName</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%UNCOMMITTED%</Value>
    </Constant>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO

---------------------------------------------------------------------------------
-- Policy: No NOLOCK in UDFs
---------------------------------------------------------------------------------
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'No NOLOCK in UDFs_ObjectSet', @facet=N'UserDefinedFunction', @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'No NOLOCK in UDFs_ObjectSet', @type_skeleton=N'Server/Database/UserDefinedFunction', @type=N'FUNCTION', @enabled=True, @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
GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'No NOLOCK in UDFs', @condition_name=N'No NOLOCK in User Defined Functions', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'No NOLOCK in UDFs_ObjectSet'
Select @policy_id
GO

----------------------------------------------------------------------------------
-- Condition: No NOLOCK in Views
----------------------------------------------------------------------------------
 Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'No NOLOCK in Views', @description=N'', @facet=N'View', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>AND</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>Schema</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%NOLOCK%</Value>
    </Constant>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>LIKE</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>String</TypeClass>
      <FunctionType>Upper</FunctionType>
      <ReturnType>String</ReturnType>
      <Count>1</Count>
      <Attribute>
        <TypeClass>String</TypeClass>
        <Name>Schema</Name>
      </Attribute>
    </Function>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>%UNCOMMITTED%</Value>
    </Constant>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

---------------------------------------------------------------------------------
-- Policy: No NOLOCK in Views
---------------------------------------------------------------------------------
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'No NOLOCK in Views_ObjectSet', @facet=N'View', @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'No NOLOCK in Views_ObjectSet', @type_skeleton=N'Server/Database/View', @type=N'VIEW', @enabled=True, @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
GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'No NOLOCK in Views', @condition_name=N'No NOLOCK in Views', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'770725f7-3817-4187-a041-6fc1f846bbc4', @execution_mode=4, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'No NOLOCK in Views_ObjectSet'
Select @policy_id
GO