Deadlocks are hard – especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain.
sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause.
sp_BlitzLock works on Azure SQL DB, Amazon RDS, and Microsoft SQL Server. Parameters include:
- @Top: Use if you want to limit the number of deadlocks to return. This is ordered by event date ascending
- @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 able. The object name has to be fully qualified ‘Database.Schema.Table’
- @StoredProcName: If you want to search for a single stored proc. The proc name has to be fully qualified ‘Database.Schema.Sproc’
- @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.
Pretty standard stuff for a Blitz script! Note that by default we look at the System Health session for deadlocks.
If you have a custom session, use the path to the files for it like so:
We can’t get any custom fields that you’ve added to a session. There didn’t seem to be a lot of joy in reverse engineering a session definition from the XE DMVs, and then constructing dynamic XQuery to parse it.
If you wanna spend your weekend on that hobby horse, well, prepare for some saddle sores.
So what does running it get you?
The first set of output looks like this:
And scrolling right…
Helpful stuff! This is all of what I’d consider useful information from the deadlock XML laid out in front of you.
And of course, we do high-level analysis of all the stuff we pull out.
The information in here can help you track down which tables and stored procs are involved in your deadlocks, and give you queries to get more information about them using our other scripts — sp_BlitzIndex for indexes and sp_BlitzCache/sp_BlitzQueryStore for queries/procs.
How to get support for sp_BlitzLock
The sp_BlitzLock documentation covers additional parameters that will return more results, store the results to a table, and more.
For free interactive support, you’ve got a few good options:
- Talk with us in the #FirstResponderKit Slack channel (if you need a free invite, hit SQLslack.com)
- Post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include the version of sp_Blitz, your SQL Server version number (including the build #), and tag it with sp_Blitz.
- To make code improvements or report a bug, visit the Github issues list and read the Contributing Guide