Locking and Blocking in SQL Server

SQL Server loves locks.
SQL Server loves locks.

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 CountersLocking Blocking Perf 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.

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.

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

Stop a trace, if needed

Step 4: Read the blocked process report data.

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

Step 5: Clean up!

Don’t forget this step.