By: Aaron Bertrand | Updated: 2021-08-03 | Comments | Related: 1 | 2 | 3 | 4 | > Locking and Blocking
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
