Blog Post

Tales of a DBA fed up with NOLOCK. Part 2

,

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.

NOLOCK_PBM_SP

NOLOCK_PBM_Triggers

NOLOCK_PBM_UDFs

NOLOCK_PBM_Views

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

Filed under: Central Management Server, Microsoft SQL Server, Policy Based Management, SQLServerPedia Syndication Tagged: Central Management Server, microsoft sql server, Policy Based Management

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads