Locking and Blocking in SQL Server
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--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 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
-- 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 |
Step 3: Look at running traces and get the trace id:
1 2 |
SELECT * from sys.traces; GO |
Stop a trace, if needed
1 2 3 4 5 |
--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 |
Step 4: Read the blocked process report data.
In this sample we use the free blocked process report viewer by Michael J Swart.
1 2 3 |
exec dbo.sp_blocked_process_report_viewer @Source='S:\Traces\BlockedProcessReportDemo.trc'; GO |
Step 5: Clean up!
Don’t forget this step.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
--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 |