Tales of a DBA fed up with NOLOCK. Part 2

7

February 23, 2015 by Kenneth Fisher

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

7 thoughts on “Tales of a DBA fed up with NOLOCK. Part 2

  1. George says:

    Thanks for the informative article. Unfortunately, the version of article at SQL Server Central does not wrap text, making it a chore to read.

    http://www.sqlservercentral.com/blogs/sqlstudies/2015/02/23/tales-of-a-dba-fed-up-with-nolock-part-2/

    Issue appears in both IE 10 and Google Chrome.

  2. Sarah says:

    For those that don’t know, it would be helpful to explain some of the consequences of using NOLOCK and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and some programming practices that are better to use instead.

    • I’ve actually got one in the works for the why not. Won’t come out for a few weeks though.

      As far as better practices that get’s really complex. That goes into the whole subject of performance tuning. Good indexing is a start, but the subject can get huge. That’s one of the reasons you see NOLOCK so often. It’s quick, it’s easy, and the down sides aren’t really obvious (nothing immediately breaks).

  3. […] a lot to put us on the naughty list. I’ve ranted several times about nolock, nolock and nolock. We have so many problems with security that it prompted me to write a session on SQL Server […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: