Finding problematic SQL NOLOCK patterns for UPDATE and DELETE

Problem

In Part 1 of this series, I showed how to use a Visitor pattern to walk through one or more T-SQL statements to identify a problematic pattern where NOLOCK hints are applied to the target of an update or delete. The method in my initial examples was very manual, though, and won't scale if this problem might be widespread. We need to be able to automate collecting a potentially large number of statements across an entire environment, and then try to eliminate false positives without manual intervention.

Solution

Let's split this up into two parts: retrieving all the pattern matching modules from a set of servers, and then storing the output in a way that we can query against that data.

To get all the procedures and triggers from a given database, you can just set a connection string, pull all the modules that match our pattern, then feed them through the Visitor pattern. Let's create a new database and a couple of simple stored procedures to demonstrate:

CREATE DATABASE BadNOLOCKs;
GO

USE BadNOLOCKS;
GO

CREATE PROCEDURE dbo.p1_false
AS
  UPDATE dbo.x SET i += 1;
  SELECT * FROM dbo.x WITH (NOLOCK);
GO

CREATE PROCEDURE dbo.p2_true
AS
  UPDATE x SET i += 1 
    FROM dbo.x WITH (NOLOCK);

  UPDATE x SET i += 5 
    FROM dbo.x
    INNER JOIN dbo.y WITH (NOLOCK)
      ON x.i = y.i; 
GO

CREATE PROCEDURE dbo.p3_true
AS
  DELETE q
    FROM dbo.x AS q WITH (NOLOCK)
    WHERE EXISTS (SELECT 1 FROM sys.objects);
  DELETE x 
    FROM dbo.x /* WITH (NOLOCK) */ AS x;
GO

To keep track of things, we need to enhance our earlier code with a few global variables, and we can add some conditionals to further eliminate false positives that our less aggressive filter initially lets through.

Add-Type -Path "C:\temp\ParseNOLOCK\Microsoft.SqlServer.TransactSql.ScriptDom.dll";
$Parser = [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser]($true)::New(); 
$Errors = [System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]]::New();
$Servers = @("192.168.1.222\SQL2019");
$Conn = New-Object System.Data.SqlClient.SqlConnection;
$global:StatementID = 0;
$SQLCommand = @"
EXEC sys.sp_executesql N'SELECT 
       ProcedureName = s.name + N''.'' + o.name + CASE 
                       WHEN t.[object_id] IS NOT NULL THEN 
                         N'' (trigger for '' + p.name + N'')'' ELSE N'''' END,
       Body          = OBJECT_DEFINITION(o.object_id)
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.schemas AS s WITH (NOLOCK) 
ON s.[schema_id] = o.[schema_id]
LEFT OUTER JOIN sys.triggers AS t WITH (NOLOCK)
ON o.[object_id] = t.[object_id]
LEFT OUTER JOIN sys.objects AS p WITH (NOLOCK)
ON t.parent_id = p.[object_id]
WHERE o.[type] IN (N''P'', N''TR'')
AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%update%from%nolock%''
     OR LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%delete%from%nolock%'');';
"@
    $Conn.ConnectionString  = "Server=$($Servers[0]);Database=BadNOLOCKs;";
    $Conn.ConnectionString += "Trusted_Connection=Yes; Integrated Security=SSPI;"
    $Conn.Open();
    $Command = $Conn.CreateCommand();
    $Command.CommandText = $SQLCommand;
    $Reader = $Command.ExecuteReader();
    while ($Reader.Read()) 
    {
        [string]$ProcedureName  = $Reader.GetValue(0).ToString();
        [string]$Body           = $Reader.GetValue(1).ToString();
        Write-Host "";
        Write-Host "***************************";
        Write-Host "New Procedure: $($ProcedureName)";
        $Fragment = $Parser.Parse([System.IO.StringReader]::New($Body), [ref]$Errors);
        $Fragment.Accept([Visitor]::New());
        $global:StatementFirstToken = 0;
        $global:StatementLastToken = 0;
    }
class Visitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor 
{
  [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragment] $Fragment)
  {
    $FragmentType   = $Fragment.GetType().Name;
    $ThisTokenStart = $Fragment.FirstTokenIndex;
    $ThisTokenEnd   = $Fragment.LastTokenIndex;
    $ThisTokenText  = $Fragment.ScriptTokenStream[$ThisTokenStart].Text;
    $InScope = $false;    
    if ($FragmentType -in ("UpdateStatement", "DeleteStatement"))
    {
        $ThisStatement = "";
        $global:StatementType = $FragmentType.Substring(0,6).ToLower();
        for ($i = $ThisTokenStart; $i -le $ThisTokenEnd; $i++)
        {
            $ThisStatement += $Fragment.ScriptTokenStream[$i].Text;
        }
        if ($ThisStatement -ilike "*from*nolock*")
        {
            $global:StatementID += 1;
            $global:StatementFirstToken = $ThisTokenStart;
            $global:StatementLastToken = $ThisTokenEnd;
            Write-Host "";
            Write-Host "New $($global:StatementType) statement ($($global:StatementID)): $($ThisStatement)";
        }
    }
    if (($ThisTokenStart -ge $global:StatementFirstToken) -and ($ThisTokenEnd -le $global:StatementLastToken))
   {
        $InScope = $true;
    }
    if (($FragmentType -in ("Identifier", "FromClause", "UpdateStatement", "DeleteStatement", "AssignmentSetClause")) -and ($InScope))
    {
        Write-Host " - TokenID $($ThisTokenStart): ($($FragmentType)): $($ThisTokenText)";
        if ((($global:StatementType -eq "delete") -and ($FragmentType -eq "FromClause")) -or
            (($global:StatementType -eq "update") -and ($FragmentType -eq "AssignmentSetClause")))
        {
            Write-Host "Target object is highest Identifier TokenID before $($ThisTokenStart)."
        }
    } 
    if (($FragmentType -eq "TableHint") -and ($ThisTokenText -ieq "NOLOCK") -and ($InScope))
    {
        Write-Host " - TokenID $($ThisTokenStart): ($($FragmentType)): $($ThisTokenText)";
        Write-Host "NOLOCK on highest Identifier TokenID before $($ThisTokenStart).";
    }
  }
}

While the body of p1_false does indeed match the pattern %update%from%nolock%, no statement within the body matched. So, we end up with no output for that procedure.

For the first statement in p2_true, we see that the target (the highest identifier before FROM) is x, and the alias with NOLOCK applied is also x, so this is a match. For the second statement, the target is x, but NOLOCK is only applied to y, so there is no match:

For the first statement in p3_true, we find a match between the NOLOCK token (q) and the first instance of a target identifier (we ignore the second one, since we are assuming we aren't going to be dealing with other patterns like CTEs just yet). The second statement only made it through our filters because there is a NOLOCK within a comment, so it will be easy to eliminate due to the absence of any actual table hints:

To get that info from all the databases on a server in one shot, you can use something like sp_ineachdb (which I wrote about here) or you can use PowerShell to collect the list of database names and then have nested loops. I'll assume you already have or can install sp_ineachdb, or can fashion your own equivalent. The only change here would be the database in the connection string, a #temp table to collect the output from each database before returning the data, and outputting a database name:

Add-Type -Path "C:\temp\ParseNOLOCK\Microsoft.SqlServer.TransactSql.ScriptDom.dll";
$Parser = [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser]($true)::New(); 
$Errors = [System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]]::New();
$Servers = @("192.168.1.222\SQL2019");
$Conn = New-Object System.Data.SqlClient.SqlConnection;
$global:StatementID = 0;
$SQLCommand = @"
CREATE TABLE #Modules
(
    ProcedureName nvarchar(512), 
    Body          nvarchar(max), 
    DatabaseName  sysname
);
EXEC master.dbo.sp_ineachdb N'INSERT #Modules(ProcedureName, Body, DatabaseName)
  SELECT ProcedureName = s.name + N''.'' + o.name + CASE 
                       WHEN t.[object_id] IS NOT NULL THEN 
                         N'' (trigger for '' + p.name + N'')'' ELSE N'''' END,
       Body          = OBJECT_DEFINITION(o.object_id),
       DatabaseName  = DB_NAME()
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.schemas AS s WITH (NOLOCK) 
ON s.[schema_id] = o.[schema_id]
LEFT OUTER JOIN sys.triggers AS t WITH (NOLOCK)
ON o.[object_id] = t.[object_id]
LEFT OUTER JOIN sys.objects AS p WITH (NOLOCK)
ON t.parent_id = p.[object_id]
WHERE o.[type] IN (N''P'', N''TR'')
AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%update%from%nolock%''
     OR LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%delete%from%nolock%'');';
SELECT ProcedureName, Body, DatabaseName FROM #Modules;
"@
    $Conn.ConnectionString  = "Server=$($Servers[$i]);Database=master;";
    $Conn.ConnectionString += "Trusted_Connection=Yes; Integrated Security=SSPI;"
    $Conn.Open();
    $Command = $Conn.CreateCommand();
    $Command.CommandText = $SQLCommand;
    $Reader = $Command.ExecuteReader();
    while ($Reader.Read()) 
    {
        [string]$ProcedureName  = $Reader.GetValue(0).ToString();
        [string]$Body           = $Reader.GetValue(1).ToString();
        [string]$DatabaseName   = $Reader.GetValue(2).ToString();
        Write-Host "";
        Write-Host "***************************";
        Write-Host "New Procedure: $($DatabaseName).$($ProcedureName)";
        $Fragment = $Parser.Parse([System.IO.StringReader]::New($Body), [ref]$Errors);
        $Fragment.Accept([Visitor]::New());
        $global:StatementFirstToken = 0;
        $global:StatementLastToken = 0;
    }
class Visitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor 
{
   # nothing changed past here

Partial output:

Finally, if we want to add more than one server to the list, we can just expand our array, ensure that all servers have sp_ineachdb, wrap the reader code in a loop, and have the connection string iterate through:

Add-Type -Path "C:\temp\ParseNOLOCK\Microsoft.SqlServer.TransactSql.ScriptDom.dll";
$Parser = [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser]($true)::New(); 
$Errors = [System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]]::New();
$Servers = @("192.168.1.222\SQL2019", "192.168.1.222\SQL2017");
$Conn = New-Object System.Data.SqlClient.SqlConnection;
$global:StatementID = 0;
$SQLCommand = @"
CREATE TABLE #Modules
(
    ProcedureName nvarchar(512), 
    Body          nvarchar(max), 
    DatabaseName  sysname, 
    ServerName    sysname
);
EXEC master.dbo.sp_ineachdb N'INSERT #Modules(ProcedureName, Body, DatabaseName, ServerName)
  SELECT ProcedureName = s.name + N''.'' + o.name + CASE 
                       WHEN t.[object_id] IS NOT NULL THEN 
                         N'' (trigger for '' + p.name + N'')'' ELSE N'''' END,
       Body          = OBJECT_DEFINITION(o.object_id),
       DatabaseName  = DB_NAME(),
       ServerName    = @@SERVERNAME
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.schemas AS s WITH (NOLOCK) 
ON s.[schema_id] = o.[schema_id]
LEFT OUTER JOIN sys.triggers AS t WITH (NOLOCK)
ON o.[object_id] = t.[object_id]
LEFT OUTER JOIN sys.objects AS p WITH (NOLOCK)
ON t.parent_id = p.[object_id]
WHERE o.[type] IN (N''P'', N''TR'')
AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%update%from%nolock%''
     OR LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%delete%from%nolock%'');';
SELECT ProcedureName, Body, DatabaseName, ServerName FROM #Modules;
"@
for ($i = 0; $i -lt $Servers.Count; $i++)
{
    $Conn.ConnectionString  = "Server=$($Servers[$i]);Database=master;";
    $Conn.ConnectionString += "Trusted_Connection=Yes; Integrated Security=SSPI;"
    $Conn.Open();
    $Command = $Conn.CreateCommand();
    $Command.CommandText = $SQLCommand;
    $Reader = $Command.ExecuteReader();
    while ($Reader.Read()) 
    {
        [string]$ProcedureName  = $Reader.GetValue(0).ToString();
        [string]$Body           = $Reader.GetValue(1).ToString();
        [string]$DatabaseName   = $Reader.GetValue(2).ToString();
        [string]$ServerName     = $Reader.GetValue(3).ToString();
        Write-Host "";
        Write-Host "***************************";
        Write-Host "New Procedure: [$($ServerName)].$($DatabaseName).$($ProcedureName)";
        $Fragment = $Parser.Parse([System.IO.StringReader]::New($Body), [ref]$Errors);
        $Fragment.Accept([Visitor]::New());
        $global:StatementFirstToken = 0;
        $global:StatementLastToken = 0;
    }
}
class Visitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor 
{
   # still nothing changed past here

Partial output:

Note that @@SERVERNAME will return what the server thinks it's called, not the name or IP you used to connect.

Now that we can collect the data from all servers in one shot, it's even more important that we stop relying on manually inspecting the output visually in order to distinguish the bad patterns from the okay patterns. In the next part, I'll show how to load the data into tables and write queries against it to get much more succinct output.

Next Steps

See these tips and other resources involving NOLOCK, parsing modules, and TSqlFragmentVisitor:

Finding problematic NOLOCK patterns in SQL Server - Part 1
SQL Server NOLOCK Anomalies, Issues and Inconsistencies
Avoid using NOLOCK on SQL Server UPDATE and DELETE statements
How to Find Keywords in SQL Server Stored Procedures and Functions
Microsoft SQL Server Script DOM
Parse parameter default values using PowerShell – Part 1 | Part 2 | Part 3

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-08-03

Finding problematic SQL NOLOCK patterns for UPDATE and DELETE - Part 2

Problem

Solution

Next Steps

About the author

Comments For This Article