As DBAs, we all know how frustrating deadlocks can be. One moment everything looks fine, and the next, SQL Server decides to kill a perfectly good query just to break the deadlock cycle. Hunting down the cause often means digging through error logs, setting up Extended Events, and piecing together XML deadlock graphs that feel more like puzzles than solutions.
That is where sp_BlitzLock comes to the rescue. It takes the pain out of deadlock analysis by reading deadlock graphs for you and turning them into a clear, easy-to-understand report. Instead of spending hours trying to figure out who blocked whom, you get a straightforward breakdown of the queries, resources, and victims involved. In short, sp_BlitzLock makes a DBA’s life much easier when dealing with deadlocks.
Figure-1: sp_BlitzLock
sp_BlitzLock
sp_BlitzLock ships with FirstResponderKit. Originally it was written by Erik Data Darling. It actually analyzes the recent deadlocks and group them by table, query, app and login. So, you can do analysis of which tables, indexes, and queries are involved in most of your deadlocks which helps to find out the root causes.
When should run it:
- sp_Blitz indicates huge deadlocks daily
- Users complaints about deadlocks error
It extracts data from system_health extended event. So, start this before using sp_BlitzLock.
Executing sp_BlitzLock
You can execute sp_BlitzLock without supplying any parameter though it supports below:
- @DatabaseName: If you want to filter to a specific database
- @StartDate: The date you want to start searching on.
- @EndDate: The date you want to stop searching on.
- @ObjectName: If you want to filter to a specific table. The object name has to be fully qualified 'Database.Schema.Table'
- @StoredProcName: If you want to search for a single stored procedure. Don't specify a schema or database name - just a stored procedure name alone is all you need, and if it exists in any schema (or multiple schemas), we'll find it.
- @AppName: If you want to filter to a specific application.
- @HostName: If you want to filter to a specific host. @LoginName: If you want to filter to a specific login.
- @EventSessionPath: If you want to point this at an XE session rather than the system health session.
Now run sp_BlitzLock without any parameter which will display 3 result sets (Figure-2).
EXEC sp_BlitzLock
Figure-2: sp_BlitzLock output
- Top: Itemized list of deadlocks of your workload.
- Middle: Plan cache of queries who participated in deadlocks.
- Bottom: The most important part which shows
- Recommendations to resolve deadlocks.
- List of tables, indexes, application name, logins, stored procedures or adhoc queries that engaged in the deadlocks
- No. of times an objected involved in deadlocks
How to Use sp_BlitzLock Output
Find out the top tables which participated in the most deadlocks from bottom part of sp_BlitzLock output and
- Optimize the tables and queries
- Create appropriate indexes on the tables. For details check my blog on indexes.
- Ensure every transaction accesses the resources in the same physical order. For example, if in transaction T1 tables are accessed in A, B and C then in transaction T2, access the tables in A, B and C order.
Final Words
Deadlocks are an unavoidable part of life for a busy SQL Server, but troubleshooting them does not have to be painful. With sp_BlitzLock, DBAs gain a powerful tool that transforms complex deadlock graphs into actionable insights. Instead of drowning in XML, you get clarity which queries were involved, what resources they fought over, and how to address the root cause. Whether you are fine-tuning queries, redesigning indexes, or improving application logic, sp_BlitzLock equips you with the right information to act quickly and confidently. By making deadlock analysis accessible and efficient, it allows DBAs to spend less time firefighting and more time optimizing their systems.
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!