Deadlocks are rough to work with. Here are the scripts I use to capture deadlocks, find which ones are reoccurring, and view them along with a couple free eBooks to resolve them.
You could have thousands of deadlocks and it would take you forever to find out which ones are reoccurring or which ones are some freak accident with an annual process. I used to read through deadlock graphs one-by-one to see what was reoccurring, and I used to be less satisfied with my job as well. If you’re going to bang your head on your desk making sure a deadlock never happens again, you’ll want to make sure it wasn’t a one-time event that wouldn’t have happened again anyways.
I’m not going to get into how to figure out deadlocks, that’s a chapter in a book more than it’s a blog post. Chapter 7 to be exact in the FREE eBook or $25 physical book for Accidental DBAs by Jonathan Kehayias and Ted Krueger. Also, a whole book on blocking by Kalen Delaney in a FREE eBook or $22 physical book. I have to admit that Ted Krueger isn’t a name I recognize. However, Jonathan and Kalen are easily on my list of top 5 best MVPs out there.
Anyways, back to me. Here’s what I use to look at deadlock traces. The base code was found on the internet years ago, and you can find it in several places now. Because of that I have no idea who the original author is, but I’d be glad to throw up credit here for anyone who can show me a site dated older than when I started using it.
There are two versions here, almost identical code in each. The first one summarizes the deadlocks and gives you a count of the occurrences. The second one shows each SPID involved in the deadlock separately along with the XML for the deadlock.
DECLARE @Path VarChar(500); SET @Path = (SELECT TOP 1 [path] FROM sys.traces WHERE [path] LIKE '%deadlock%') --SET @Path = 'D:\Perflogs\PerfTraces\DeadlockTrace.trc' ;with CTE as ( select [TraceID] = 3, [RowID] = row_number() OVER (ORDER BY StartTime), -- assign a row number to each deadlock [DeadlockGraph]=case when TextData like '<deadlock-list%' then convert(xml, TextData) else null end, * from ::fn_trace_gettable(@path, default) where TextData like '<deadlock-list%' --AND StartTime BETWEEN '2013-06-17 00:00' AND '2013-06-30 00:00' ) SELECT [Procedure], Inputbuffer, dMonth = DatePart(Month, DeadLockTime), dDay = DatePart(Day, DeadLockTime), DeadlockCount = Count(*), dMax = max(DeadlockTime), dMin = min(DeadlockTime) FROM ( select [TimeoutID] = CTE.RowID, [DeadlockTime] = [StartTime], [DeadlockGraph], [PagelockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'), [DeadlockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'), [Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end, [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'), [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'), [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'), [HostPID] = Deadlock.Process.value('@hostpid', 'varchar(20)'), [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'), [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'), [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') from CTE CROSS APPLY CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process) ) X --WHERE Victim = 0 --WHERE TimeoutID IN (SELECT RowID FROM CTE WHERE [PROCEDURE] = 'master.dbo.FakeProcName') --WHERE inputbuffer like '%select top 10%' GROUP BY [procedure], Inputbuffer, DatePart(Month, DeadLockTime), DatePart(Day, DeadLockTime) --ORDER BY TimeoutID
Then there’s this to actually let me look at the XML. Although you can easily see in the script below which deadlocks reoccur back-to-back, it’s not so easy to see reoccurrence of deadlocks that happen once every morning between 2 and 3 AM. That’s where the first script shines, and you should use and abuse it.
DECLARE @Path VarChar(500); SET @Path = (SELECT TOP 1 [path] FROM sys.traces WHERE [path] LIKE '%deadlock%'); with CTE as ( select [TraceID] = 3, [RowID] = row_number() OVER (ORDER BY StartTime), -- assign a row number to each deadlock [DeadlockGraph]=case when TextData like '<deadlock-list%' then convert(xml, TextData) else null end, * from ::fn_trace_gettable(@Path, default) where TextData like '<deadlock-list%' --AND StartTime BETWEEN '2013-03-08 02:00' AND '2013-03-08 04:00' AND StartTime > DATEADD(Hour, -2, GetDate()) ) SELECT * FROM ( select [TimeoutID] = CTE.RowID, [DeadlockTime] = [StartTime], [DeadlockGraph], [PagelockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'), [DeadlockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'), [Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end, [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'), [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), [ClientApp] = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29) WHEN 'SQLAgent - TSQL JobStep (Job ' THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67) ELSE Deadlock.Process.value('@clientapp', 'varchar(100)') END , [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'), [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'), [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'), [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') from CTE CROSS APPLY CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process) -- ORDER BY [DeadlockObject], [ClientApp], [Procedure], [Code] ) X --WHERE Victim = 0 --WHERE [Procedure] = 'master.dbo.FakeProcedureName' ORDER BY TimeoutID DESC
Finally, to make all of this happen you’ll need a deadlock trace. Don’t go thinking that I open profiler, make a deadlock trace, script it out, and run it every time I need one. Reusable code is awesome, and this is reused every time I see an alert come through that we are having too many deadlocks. I have this proc in our Perf database, which is on every SQL Server we manage. So you don’t have to dig through the numbers, it grabs every event for the deadlock graph and nothing else.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ServerSideTrace_Deadlock]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_ServerSideTrace_Deadlock] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[usp_ServerSideTrace_Deadlock] @FilePath nvarchar(1000) = N'D:\Perflogs\PerfTraces\DeadlockTrace', @maxfilesize bigint = 25, @maxfiles int = 10, @TraceDBID bit=0, @DBID int=0 AS declare @rc int declare @TraceID int --Create Trace exec @rc = sp_trace_create @TraceID output, 2, @FilePath, @maxfilesize, NULL, @maxfiles if (@rc != 0) Begin select ErrorCode=@rc return End -- Set the RPC Completed and SQL Batch Completed events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 148, 11, @on exec sp_trace_setevent @TraceID, 148, 12, @on exec sp_trace_setevent @TraceID, 148, 14, @on exec sp_trace_setevent @TraceID, 148, 1, @on exec sp_trace_setevent @TraceID, 25, 15, @on exec sp_trace_setevent @TraceID, 25, 8, @on exec sp_trace_setevent @TraceID, 25, 32, @on exec sp_trace_setevent @TraceID, 25, 56, @on exec sp_trace_setevent @TraceID, 25, 1, @on exec sp_trace_setevent @TraceID, 25, 9, @on exec sp_trace_setevent @TraceID, 25, 57, @on exec sp_trace_setevent @TraceID, 25, 2, @on exec sp_trace_setevent @TraceID, 25, 10, @on exec sp_trace_setevent @TraceID, 25, 3, @on exec sp_trace_setevent @TraceID, 25, 11, @on exec sp_trace_setevent @TraceID, 25, 12, @on exec sp_trace_setevent @TraceID, 25, 13, @on exec sp_trace_setevent @TraceID, 25, 6, @on exec sp_trace_setevent @TraceID, 25, 14, @on exec sp_trace_setevent @TraceID, 25, 22, @on exec sp_trace_setevent @TraceID, 59, 32, @on exec sp_trace_setevent @TraceID, 59, 56, @on exec sp_trace_setevent @TraceID, 59, 1, @on exec sp_trace_setevent @TraceID, 59, 57, @on exec sp_trace_setevent @TraceID, 59, 2, @on exec sp_trace_setevent @TraceID, 59, 14, @on exec sp_trace_setevent @TraceID, 59, 22, @on exec sp_trace_setevent @TraceID, 59, 3, @on exec sp_trace_setevent @TraceID, 59, 12, @on -- Set the Filters --Default to exclude the trace from Tracing Itself exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler' --Add other filters as specified by parameters If @TraceDBID=1 exec sp_trace_setfilter @TraceID, 3, 0, 0, @DBID -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references return @TraceID GO
The basics of a deadlock is that process 1 gets a lock on data A while process 2 gets a lock on data B. Then process 1 says it needs a lock on data B to continue while process 2 needs a lock on data A to continue. It’s a simple basic concept, but it gets complex…too complex for me to cover it here and say I did it justice. Sometimes a process deadlocks on itself, doing a parallelism deadlock as demonstrated by Jason Strate. Solutions also vary from order of operations (lets make processes 1 and 2 both get data A first, then they can’t deadlock), tuning (if the locks are released quicker then there’s less time for deadlocks), reducing the number of transactions (the lock on data A was grabbed earlier in this transaction, and there’s no reason these need to be in a explicit transaction), scheduling (these are both processes that have to run between 10 PM and 6 AM, why do they both start at midnight?), and many other approaches. There isn’t one right answer, and anyone promising a single right answer in one short blog post is selling you short. That’s either a very long blog post or it’s an incomplete answer. I go with option 3, and here’s my post on the best way to view them along with a couple book chapters to get you started on fixing what you found.
Related Content:
SQL Server Concurrency: Locking, Blocking and Row Versioning By Kalen Delaney
FREE eBook or $22 physical book
Troubleshooting SQL Server: A Guide for the Accidental DBA By Jonathan Kehayias and Ted Krueger
FREE eBook or $25 physical book
Filed under: Scripts, SQL Server, Troubleshooting Tagged: Blocking, Deadlock, Trace 
