Find Problematic SQL NOLOCK Patterns in SQL Server DML Statements - Part 4

By:   |   Updated: 2021-09-01   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Locking and Blocking


Problem

In the previous parts of this series (part 1 | 2 | 3), I showed how to use a Visitor pattern to identify problematic NOLOCK hints – without false positives – in DML statements in procedures and triggers across your environment. In this post, I wanted to show how you can also include DML statements that have come in via ad hoc queries and application code.

Solution

We can make a minor adjustment to our previous PowerShell code to include these ad hoc queries in our analysis, but we won’t be able to tell anything more about them immediately (for example, what database they affect). To illustrate, let’s create a basic table like this:

CREATE TABLE dbo.TestingAdHoc
(
    i int
);
GO

Then run an update like this:

UPDATE tah SET i = 4 
  FROM dbo.TestingAdHoc AS tah WITH (NOLOCK);

I can manually find that plan in the plan cache with the following query (taking care to exclude anything we’re already capturing from modules):

SELECT Body = SUBSTRING(t.text, s.statement_start_offset / 2 + 1, 
              (CASE WHEN s.statement_end_offset = -1 
               THEN LEN(CONVERT(nvarchar(max), t.text)) * 2 
               ELSE s.statement_end_offset END - s.statement_start_offset) / 2 + 2)
  FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
  WHERE NOT EXISTS 
    (
      SELECT 1 FROM sys.dm_exec_cached_plans AS cp
        WHERE cp.objtype = N'Proc'
          AND cp.plan_handle = s.plan_handle
    )
    AND (LOWER(t.text) LIKE N'%update%from%nolock%'
     OR  LOWER(t.text) LIKE N'%delete%from%nolock%');

Output:

Output of query against plan cache

If we want to associate a "procedure name" with this so we can identify it later, we can pull out the query hash (and number them in case there are duplicates), and apply a generic database name of #AdHocQueryPlans#: 

SELECT ProcedureName = 'QueryHash: ' + CONVERT(nvarchar(64), s.query_hash, 1) + ' : ' 
                     + RTRIM(ROW_NUMBER() OVER (PARTITION BY s.query_hash ORDER BY @@SPID)),
       Body = SUBSTRING(t.text, s.statement_start_offset / 2 + 1, 
              (CASE WHEN s.statement_end_offset = -1 
               THEN LEN(CONVERT(nvarchar(max), t.text)) * 2 
               ELSE s.statement_end_offset END - s.statement_start_offset) / 2 + 2),
       DatabaseName = N'#AdHocQueryPlans#', 
       ServerName = @@SERVERNAME
  FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
  WHERE NOT EXISTS 
    (
      SELECT 1 FROM sys.dm_exec_cached_plans AS cp
        WHERE cp.objtype = N'Proc'
          AND cp.plan_handle = s.plan_handle
    )
    AND (LOWER(t.text) LIKE N'%update%from%nolock%'
     OR  LOWER(t.text) LIKE N'%delete%from%nolock%');

Output:

Output from plan cache including query hash

Now we can change our PowerShell to incorporate this query as well, and pass along any new statements we should analyze from the ad hoc portion of the plan cache (new part in bold):

#Add-Type -Path "C:\temp\ParseNOLOCK\Microsoft.SqlServer.TransactSql.ScriptDom.dll";
#region setup
$Parser = [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser]($true)::New(); 
$Errors = [System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]]::New();
$CollectionString = "Server=192.168.1.222\SQL2019;Database=Utility";
$Servers = @("192.168.1.222\SQL2019");
$Conn = New-Object System.Data.SqlClient.SqlConnection;
$global:StatementID = 0;
$global:LastModuleID = 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%'');';

INSERT #Modules(ProcedureName, Body, DatabaseName, ServerName)
SELECT ProcedureName = 'QueryHash: ' + CONVERT(nvarchar(64), s.query_hash, 1) + ' : '
                     + RTRIM(ROW_NUMBER() OVER (PARTITION BY s.query_hash ORDER BY @@SPID)),
       Body = SUBSTRING(t.text, s.statement_start_offset / 2 + 1,
              (CASE WHEN s.statement_end_offset = -1
               THEN LEN(CONVERT(nvarchar(max), t.text)) * 2
               ELSE s.statement_end_offset END - s.statement_start_offset) / 2 + 2),
       DatabaseName = N'#AdHocQueryPlans#',
       ServerName = @@SERVERNAME
       FROM sys.dm_exec_query_stats AS s 
       CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
       WHERE NOT EXISTS
          (SELECT 1 FROM sys.dm_exec_cached_plans AS cp
            WHERE cp.objtype = N'Proc' 
            AND cp.plan_handle = s.plan_handle
          ) 
          AND (LOWER(t.text) LIKE N'%update%from%nolock%' 
          OR LOWER(t.text) LIKE N'%delete%from%nolock%');
          
SELECT ProcedureName, Body, DatabaseName, ServerName FROM #Modules;
"@
function Cleanup()
{
    try
    {
        $Conn = New-Object System.Data.SqlClient.SqlConnection;
        $Conn.ConnectionString  = $CollectionString;
        $Conn.ConnectionString += "Trusted_Connection=Yes; Integrated Security=SSPI;"
        $Conn.Open();
        $Command = $Conn.CreateCommand();
        $Command.CommandType = [System.Data.CommandType]::StoredProcedure;
        $Command.CommandText = "dbo.Nolock_Wipeout";
        $Command.ExecuteNonQuery() > $null;
    }
    catch
    {
        Write-Host "Failed inside cleanup ($($Conn.ConnectionString)).`n$PSItem";            
    }
    finally
    {
        $Conn.Close() > $null;
    }      
}
function WriteARow_Procedure
(
    [string]$Server,     
    [string]$Database,     
    [string]$Procedure,     
    [string]$Body     
)
{
    try
    {
        $Conn = New-Object System.Data.SqlClient.SqlConnection;
        $Conn.ConnectionString  = $CollectionString;
        $Conn.ConnectionString += "Trusted_Connection=Yes; Integrated Security=SSPI;"
        $Conn.Open();
        $Command = $Conn.CreateCommand();
        $Command.CommandType = [System.Data.CommandType]::StoredProcedure;
        $Command.CommandText = "dbo.Nolock_AddProcedure";
        $p1 = $Command.Parameters.Add("@ServerName",    [Data.SqlDbType]::NVarChar,  513).Value = $Server;
        $p2 = $Command.Parameters.Add("@DatabaseName",  [Data.SqlDbType]::NVarChar,  255).Value = $Database;
        $p3 = $Command.Parameters.Add("@ProcedureName", [Data.SqlDbType]::NVarChar, 1024).Value = $Procedure;
        $p4 = $Command.Parameters.Add("@Body",          [Data.SqlDbType]::NVarChar,   -1).Value = $Body;
        $global:LastModuleID = $Command.ExecuteScalar();
    }
    catch
    {
        Write-Host "Failed inside write a row ($($Conn.ConnectionString)).`n$PSItem";            
    }
    finally
    {
        $Conn.Close() > $null;
    }      
}
function WriteARow_Statement
(
    [string]$StatementType,
    [string]$StatementText,     
    [int]$LineNumber,     
    [int]$TokenStart,     
    [int]$TokenEnd
)
{   
    try
    {
        $global:LastStatementID += 1;
        $Conn = New-Object System.Data.SqlClient.SqlConnection;
        $Conn.ConnectionString  = $CollectionString;
        $Conn.ConnectionString += "Trusted_Connection=Yes; Integrated Security=SSPI;"
        $Conn.Open();
        $Command = $Conn.CreateCommand();
        $Command.CommandType = [System.Data.CommandType]::StoredProcedure;
        $Command.CommandText = "dbo.Nolock_AddStatement";
        $p1 = $Command.Parameters.Add("@ModuleID",      [Data.SqlDbType]::Int).Value = $global:LastModuleID;
        $p2 = $Command.Parameters.Add("@StatementID",   [Data.SqlDbType]::Int).Value = $global:LastStatementID;
        $p3 = $Command.Parameters.Add("@StatementType", [Data.SqlDbType]::NVarChar, 255).Value = $StatementType;
        $p4 = $Command.Parameters.Add("@StatementText", [Data.SqlDbType]::NVarChar,  -1).Value = $StatementText;
        $p5 = $Command.Parameters.Add("@LineNumber",    [Data.SqlDbType]::Int).Value = $LineNumber;
        $p6 = $Command.Parameters.Add("@TokenStart",    [Data.SqlDbType]::Int).Value = $TokenStart;
        $p7 = $Command.Parameters.Add("@TokenEnd",      [Data.SqlDbType]::Int).Value = $TokenEnd;
        $Command.ExecuteNonQuery() > $null;
    }
    catch
    {
        Write-Host "Failed inside write a row ($($Conn.ConnectionString)).`n$PSItem";            
    }
    finally
    {
        $Conn.Close() > $null;
    }      
}
function WriteARow_Token
(
    [int]$TokenID,     
    [string]$TokenType,     
    [string]$TokenText,
    [int]$IsTargetToken,
    [int]$IsHintToken
)
{
    try
    {
        $Conn = New-Object System.Data.SqlClient.SqlConnection;
        $Conn.ConnectionString  = $CollectionString;
        $Conn.ConnectionString += "Trusted_Connection=Yes; Integrated Security=SSPI;"
        $Conn.Open();
        $Command = $Conn.CreateCommand();
        $Command.CommandType = [System.Data.CommandType]::StoredProcedure;
        $Command.CommandText = "dbo.Nolock_AddToken";
        $p1 = $Command.Parameters.Add("@ModuleID",     [Data.SqlDbType]::Int).Value = $global:LastModuleID;
        $p2 = $Command.Parameters.Add("@StatementID",  [Data.SqlDbType]::Int).Value = $global:LastStatementID;
        $p3 = $Command.Parameters.Add("@TokenID",      [Data.SqlDbType]::Int).Value = $TokenID;
        $p4 = $Command.Parameters.Add("@TokenType",    [Data.SqlDbType]::NVarChar, 255).Value = $TokenType;
        $p5 = $Command.Parameters.Add("@TokenText",    [Data.SqlDbType]::NVarChar, -1).Value = $TokenText;
        $p6 = $Command.Parameters.Add("@IsTargetToken",[Data.SqlDbType]::TinyInt).Value = $IsTargetToken;
        $p7 = $Command.Parameters.Add("@IsHintToken",  [Data.SqlDbType]::TinyInt).Value = $IsHintToken;
        $Command.ExecuteNonQuery()   > $null;
    }
    catch
    {
        Write-Host "Failed inside write a row ($($Conn.ConnectionString)).`n$PSItem";            
    }
    finally
    {
        $Conn.Close() > $null;
    }      
}
#endregion setup
Cleanup;
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();
        WriteARow_Procedure -Server $ServerName -Database $DatabaseName -Procedure $ProcedureName -Body $Body;
        $Fragment = $Parser.Parse([System.IO.StringReader]::New($Body), [ref]$Errors);
        $Fragment.Accept([Visitor]::New());
        $global:StatementFirstToken = 0;
        $global:StatementLastToken = 0;
    }
    Write-Host "Wrote $($global:LastModuleID) procedures.";
}
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;
            WriteARow_Statement -LastModuleID $global:LastModuleID -StatementType $global:StatementType -StatementText `
                      $ThisStatement -LineNumber $Fragment.StartLine -TokenStart $ThisTokenStart -TokenEnd $ThisTokenEnd;
        }
    }
    if (($ThisTokenStart -ge $global:StatementFirstToken) -and ($ThisTokenEnd -le $global:StatementLastToken))
    {
        $InScope = $true;
    }
    if (($FragmentType -in ("Identifier","FromClause","UpdateStatement","DeleteStatement","AssignmentSetClause")) -and ($InScope))
    {
      $TargetToken = 0;
      if ((($global:StatementType -eq "delete") -and ($FragmentType -eq "FromClause")) -or
          (($global:StatementType -eq "update") -and ($FragmentType -eq "AssignmentSetClause")))
      {
          $TargetToken = 1;
      }
      WriteARow_Token -TokenID $ThisTokenStart -TokenType $FragmentType -TokenText $ThisTokenText `
            -TargetToken $TargetToken -HintToken 0;
    } 
    if (($FragmentType -eq "TableHint") -and ($ThisTokenText -ieq "NOLOCK") -and ($InScope))
    {
      WriteARow_Token -TokenID $ThisTokenStart -TokenType $FragmentType -TokenText $ThisTokenText `
            -TargetToken 0 -HintToken 1;
    }
  }
}

Now when we run the code and query the output:

SELECT --p.ServerName, 
       p.DatabaseName, 
       p.ProcedureName, 
       s.LineNumber, 
       TargetAlias = t.Target_of_DML, 
       s.StatementText
  FROM dbo.ReferencedElements AS t 
  INNER JOIN dbo.NolockHintedElements AS n 
    ON t.StatementID = n.StatementID
    AND t.ModuleID = n.ModuleID
    AND LOWER(t.Target_of_DML) = LOWER(NOLOCK_applies_to)
  INNER JOIN dbo.NolockStatementList AS s 
    ON n.StatementID = s.StatementID 
    AND n.ModuleID = s.ModuleID
  INNER JOIN dbo.NolockProcedureList AS p 
    ON p.ModuleID = s.ModuleID
  ORDER BY p.ProcedureName, s.LineNumber;

Output:

Output including DML statements from ad hoc query plans

You might get some more noise in there than you expected, and it may take some additional work to track down the source of those queries, but at least now you know that they are happening and can make an initial guess at severity and risk.

Next Steps

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



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand 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-09-01

Comments For This Article

















get free sql tips
agree to terms