I've been tasked with investigating an issue where an application's job has failed due to deadlocks. I'm aware of using traces to capture deadlocks, however as this is something that occurred 16 hours ago I wanted to know if it was possible to see what caused the deadlock?
Thanks
Best Answer
Start with sp_BlitzLock. It's a free open source stored procedure written by Erik Darling that examines the built-in System Health Extended Events session in SQL Server 2012 & newer.
You can download it from the First Responder Kit Github repository.
To install it, just run sp_BlitzLock.sql to install the stored procedure, then run:
The first result set is a set of deadlock details, and the second is analysis that looks at which apps, users, and tables are most commonly involved in your deadlocks. It also gives you parameters to use for sp_BlitzCache to analyze their query plans, and sp_BlitzIndex to analyze over-indexing and under-indexing issues on the tables that may be contributing.