![]() It’s a simple basic concept, but it gets complex…too complex for me to cover it here and say I did it justice. Then process 1 says it needs a lock on data B to continue while process 2 needs a lock on data A to continue. Return basics of a deadlock is that process 1 gets a lock on data A while process 2 gets a lock on data B. Add other filters as specified by parametersĮxec sp_trace_setfilter, 3, 0, 0, Set the trace status to start Default to exclude the trace from Tracing ItselfĮxec sp_trace_setfilter, 10, 0, 7, N'SQL Server Profiler' Select ErrorCode Set the RPC Completed and SQL Batch Completed eventsĮxec sp_trace_setevent, 148, 11, sp_trace_setevent, 148, 12, sp_trace_setevent, 148, 14, sp_trace_setevent, 148, 1, sp_trace_setevent, 25, 15, sp_trace_setevent, 25, 8, sp_trace_setevent, 25, 32, sp_trace_setevent, 25, 56, sp_trace_setevent, 25, 1, sp_trace_setevent, 25, 9, sp_trace_setevent, 25, 57, sp_trace_setevent, 25, 2, sp_trace_setevent, 25, 10, sp_trace_setevent, 25, 3, sp_trace_setevent, 25, 11, sp_trace_setevent, 25, 12, sp_trace_setevent, 25, 13, sp_trace_setevent, 25, 6, sp_trace_setevent, 25, 14, sp_trace_setevent, 25, 22, sp_trace_setevent, 59, 32, sp_trace_setevent, 59, 56, sp_trace_setevent, 59, 1, sp_trace_setevent, 59, 57, sp_trace_setevent, 59, 2, sp_trace_setevent, 59, 14, sp_trace_setevent, 59, 22, sp_trace_setevent, 59, 3, sp_trace_setevent, 59, 12, Set the Filters ĬREATE procedure nvarchar (1000 ) = N'D:\Perflogs\PerfTraces\DeadlockTrace' bigint = 25 int = 10 bit =0 int =0Įxec = sp_trace_create output, 2, ,, NULL, != 0 ) objects WHERE object_id = OBJECT_ID ( N'.' ) AND type in ( N'P', N'PC' ))ĭROP PROCEDURE. So you don’t have to dig through the numbers, it grabs every event for the deadlock graph and nothing else. I have this proc in our Perf database, which is on every SQL Server we manage. Reusable code is awesome, and this is reused every time I see an alert come through that we are having too many deadlocks. Don’t go thinking that I open profiler, make a deadlock trace, script it out, and run it every time I need one. ![]() ![]() WHERE = ''įinally, to make all of this happen you’ll need a deadlock trace. value ( 'inputbuf', 'varchar(1000)' )ĬROSS APPLY CTE.nodes ( '/deadlock-list/deadlock/process-list/process' ) as Deadlock (Process ) THEN 'SQLAgent Job: ' + ( SELECT name FROM msdb. value ( 'executionStack/frame', 'varchar(1000)' ), value (, 'varchar(50)' ) = CTE.value (, 'varchar(50)' ) then 1 else 0 end , = case when TextData like ' DATEADD ( Hour, -2, GetDate ()) = row_number () OVER ( ORDER BY StartTime ), - assign a row number to each deadlock The second one shows each SPID involved in the deadlock separately along with the XML for the deadlock. The first one summarizes the deadlocks and gives you a count of the occurrences. There are two versions here, almost identical code in each. 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. The base code was found on the internet years ago, and you can find it in several places now. Here’s what I use to look at deadlock traces. However, Jonathan and Kalen are easily on my list of top 5 best MVPs out there.Īnyways, back to me. I have to admit that Ted Krueger isn’t a name I recognize. Also, a whole book on blocking by Kalen Delaney in a FREE eBook or $22 physical book. Chapter 7 to be exact in the FREE eBook or $25 physical book for Accidental DBAs by Jonathan Kehayias and Ted Krueger. 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. ![]() 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 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. 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. 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |