Locking and Blocking in SQL Server

SQL Server uses “pessimistic” locking by default– your queries are naturally defensive. This can lead to blocking problems that keep you from scaling SQL Server.

Free video training class – before you troubleshoot blocking and deadlocks, start with How to Think Like the Engine. It’s a fast-paced 90-minute class that explains how clustered and nonclustered index internals work, and that’s vital to understanding how indexing affects blocking. It’s free – because we believe every SQL Server professional should start their training here. It really is that important – good stuff.

Tools to Diagnose Lock Contention

“Do I have lock waits right now?” Use sp_BlitzFirst® to diagnose if lock waits are currently impacting your SQL Server.

“Which tables have the worst blocking problems?” Run sp_BlitzIndex® and look for “Aggressive Index Disorder” to identify which tables have had high amounts of lock waits (for tables and indexes currently in memory).

“How do I make sense of the Blocked Process Report?” When you capture the SQL Server Blocked Process Report (scripts below), Michael J Swart’s Blocked Process Report Viewer is a free tool that helps you interpret the results.

“What if my application doesn’t catch deadlock errors?” Amit Banerjee from Microsoft’s CSS team helps you query SQL Server’s extended events System Health session for details (SQL Server 2008+).

Lock Related Performance Counters

These performance counters can be very useful to configure a perf counter based SQL Server Agent Alert so that SQL Server can notify you when blocking passes thresholds you set.

These are listed OBJECT first, then COUNTER
SQL Server: General Statistics – Processes Blocked
SQL Server: Locks – Lock Wait Time (ms)
SQL Server: Locks – Number of Deadlocks/sec

Learn about more perf counters in SQL Server here.

Read Articles about Locking, Isolation Levels, and Deadlocks

Read an introduction to two-phase locking and deadlocks from Jeremiah.

Learn how you can test code changes to find out what locks are required from Kendra.

Jeremiah teaches you how to find and prevent deadlocks.

Learn how to implement optimistic locking in SQL Server from Kendra.

Read more about isolation levels in SQL Server — check out our page of comprehensive links and references.

Scripts to use the Blocked Process Report

The “Blocked Process Report” has been around a long time in SQL Server– and it’s still a useful tool. By default in SQL Server, the deadlock detector wakes up every 5 seconds to check if your queries are in a “deadly embrace”. You can turn on an option that asks SQL Server to use this resource to check for long term blocking, and issue a report.

WARNING: You should only configure the blocked process report to be issued for values of five seconds or higher. Microsoft warns that if you set this from 1-4, you could cause the deadlock detector to run continuously and kill your performance.

To use the Blocked Process Report, you need to configure a trace that looks for the “Blocked Process Report” event under “Errors and Warnings”. We recommend using a server side trace– and of course only running this when you need it.

Want to see a demo of this? Watch one here.

Scripts in this section are samples of common ways to do these tasks– you must test and review accordingly for your environment, and run at your own risk.

Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds or longer.

--Make sure you don't have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO

exec sp_configure 'blocked process threshold (s)', 20;
GO
RECONFIGURE
GO

--Make sure you don't have any pending changes

SELECT *

FROM sys.configurations

WHERE value <> value_in_use;

exec sp_configure 'show advanced options', 1;

RECONFIGURE

exec sp_configure 'blocked process threshold (s)', 20;

RECONFIGURE

Step 2: Set up a trace to capture the blocked process report. Run it as a server side trace.

It should contain one event: Errors and Warnings -> Blocked Process Report.
It needs just two columns: Text, spid

This sample server side trace will run for five minutes and automatically stop. You must change the path to a valid directory SQL Server has permission to write to.

-- Created by: SQL Server 2012 Profiler
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

---------Added a function here:
set @DateTime = DATEADD(mi,5,getdate()); /* Run for five minutes */
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

-----------Set my filename here:
exec @rc = sp_trace_create @TraceID output, 0, N'S:\Traces\BlockedProcessReportDemo', @maxfilesize, @Datetime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

-- Created by: SQL Server 2012 Profiler

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @DateTime datetime

---------Added a function here:

set @DateTime = DATEADD(mi,5,getdate()); /* Run for five minutes */

set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

-----------Set my filename here:

exec @rc = sp_trace_create @TraceID output, 0, N'S:\Traces\BlockedProcessReportDemo', @maxfilesize, @Datetime

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 137, 1, @on

exec sp_trace_setevent @TraceID, 137, 12, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

Step 3: Look at running traces and get the trace id:

SELECT * from sys.traces;
GO

1 2	SELECT * from sys.traces; GO

Stop a trace, if needed

--Plug in the correct traceid from the query above
EXEC sp_trace_setstatus @traceid =2, @status = 0; -- stop trace (assuming it's trace ID 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; -- close trace (assuming it's trace ID 2)
GO

--Plug in the correct traceid from the query above

EXEC sp_trace_setstatus @traceid =2, @status = 0; -- stop trace (assuming it's trace ID 2)

EXEC sp_trace_setstatus @traceid =2, @status = 2; -- close trace (assuming it's trace ID 2)

Step 4: Read the blocked process report data.

In this sample we use the free blocked process report viewer by Michael J Swart.

exec dbo.sp_blocked_process_report_viewer
@Source='S:\Traces\BlockedProcessReportDemo.trc';
GO

exec dbo.sp_blocked_process_report_viewer

@Source='S:\Traces\BlockedProcessReportDemo.trc';

Step 5: Clean up!

Don’t forget this step.

--Make sure your trace is gone
SELECT * from sys.traces;
GO

--Turn off the blocked process report when you're not using it.
--Make sure you don't have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO

exec sp_configure 'blocked process threshold (s)', 0;
GO
RECONFIGURE
GO

exec sp_configure 'blocked process threshold (s)';
GO

--Make sure your trace is gone

SELECT * from sys.traces;

--Turn off the blocked process report when you're not using it.

--Make sure you don't have any pending changes

SELECT *

FROM sys.configurations

WHERE value <> value_in_use;

exec sp_configure 'blocked process threshold (s)', 0;

RECONFIGURE

exec sp_configure 'blocked process threshold (s)';