By: Aaron Bertrand | 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:
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:
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:
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:
- Check out earlier tips in this series
- Check out these related articles
- 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-09-01
