SQL Server includes an Extended Events session called system_health<\/strong>, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the \u201cvictim,\u201d rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health<\/strong> session\u2019s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.<\/p>\n Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.<\/p>\n Every deadlock report is wrapped in a These sections collectively describe the details of the deadlock and help you identify the root cause.<\/p>\n Key takeaway<\/strong>: If this is your session or query, it means SQL Server chose it to be rolled back. Often, short-running or lower-priority transactions become victims.<\/p>\n In a deadlock, at least two processes are involved. Each Similarly, another When these two sessions simultaneously attempt to update each other\u2019s tables (A and B), a cyclical locking scenario is created, leading to a deadlock.<\/p>\n Here you see two keylock<\/strong> elements, each representing a locked resource:<\/p>\n This cyclical pattern of \u201cowner\u201d and \u201cwaiter\u201d is the hallmark of a deadlock: each process owns a lock the other needs, and neither can proceed until the other releases it. The By reviewing these deadlock reports, you can pinpoint the processes, resources, and queries involved, then make changes to prevent or reduce future deadlocks.<\/p>\n Deadlocks are a normal occurrence in high-concurrency systems but must be managed effectively. The system_health<\/strong> Extended Events session captures snapshots of the involved processes, locks, and queries. By understanding how to read the SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the \u201cvictim,\u201d rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session\u2019s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[35,12,324,91,73],"tags":[453,450,451,353,452,131,402,448,449],"class_list":["post-942","post","type-post","status-publish","format-standard","hentry","category-data-integrity","category-internals","category-locking","category-query-optimization","category-troubleshooting","tag-concurrency-issues","tag-database-troubleshooting","tag-deadlock","tag-extended-events","tag-locking-mechanisms","tag-sql-server","tag-sql-server-management-studio","tag-system_health","tag-xml-deadlock-report"],"yoast_head":"\n1. The <deadlock> Root Element<\/h2>\n
<deadlock>\n ...\n<\/deadlock><\/code><\/pre>\n<deadlock><\/code> element. Inside, you\u2019ll see:<\/p>\n\n
2. The <victim-list> Section<\/h2>\n
<victim-list>\n <victimProcess id=\"process292009b2ca8\"\/>\n<\/victim-list><\/code><\/pre>\n\n
id<\/code>) of the session chosen as the deadlock victim. SQL Server terminates this session\u2019s transaction to resolve the deadlock. Here, process292009b2ca8<\/code> is the victim.<\/li>\n<\/ul>\n3. The <process-list> Section<\/h2>\n
<process-list>\n <process id=\"process292009b2ca8\" ... spid=\"87\" ... >\n ...\n <\/process>\n <process id=\"process292009bb088\" ... spid=\"61\" ... >\n ...\n <\/process>\n<\/process-list><\/code><\/pre>\n<process><\/code> element provides information such as:<\/p>\n\n
read committed<\/code>).<\/li>\nExample Interpretation<\/h3>\n
<process id=\"process292009b2ca8\" ... spid=\"87\" ... >\n <executionStack>\n <frame procname=\"testdb.dbo.sp_UpdateTableB\" line=\"33\" ...>\n UPDATE dbo.TableA\n SET Column1 = CONCAT('UpdatedA_by_Session_', @@SPID)\n WHERE ID = @I\n <\/frame>\n <frame procname=\"adhoc\" line=\"2\" ...>\n EXEC dbo.sp_UpdateTableB @ID = 1, @DelaySeconds = 1\n <\/frame>\n <\/executionStack>\n <inputbuf>\n EXEC dbo.sp_UpdateTableB @ID = 1, @DelaySeconds = 12;\n <\/inputbuf>\n<\/process><\/code><\/pre>\n\n
spid=\"87\"<\/code> is running a stored procedure named sp_UpdateTableB<\/strong>, which in turn updates TableA<\/strong>.<\/li>\ninputbuf<\/code> tells us the exact command that was sent: EXEC dbo.sp_UpdateTableB @ID = 1, @DelaySeconds = 12;<\/code><\/li>\n<\/ul>\n<process><\/code> element (spid=\"61\"<\/code>) is calling sp_UpdateTableA<\/strong>, which updates TableB<\/strong>.<\/p>\n4. The <resource-list> Section<\/h2>\n
<resource-list>\n <keylock hobtid=\"72057594046382080\" dbid=\"20\" objectname=\"testdb.dbo.TableA\" ...>\n <owner-list>\n <owner id=\"process292009bb088\" mode=\"X\"\/>\n <\/owner-list>\n <waiter-list>\n <waiter id=\"process292009b2ca8\" mode=\"X\" requestType=\"wait\"\/>\n <\/waiter-list>\n <\/keylock>\n <keylock hobtid=\"72057594046447616\" dbid=\"20\" objectname=\"testdb.dbo.TableB\" ...>\n <owner-list>\n <owner id=\"process292009b2ca8\" mode=\"X\"\/>\n <\/owner-list>\n <waiter-list>\n <waiter id=\"process292009bb088\" mode=\"X\" requestType=\"wait\"\/>\n <\/waiter-list>\n <\/keylock>\n<\/resource-list><\/code><\/pre>\n\n
\n
process292009bb088<\/code> (spid=\"61\"<\/code>).<\/li>\nprocess292009b2ca8<\/code> (spid=\"87\"<\/code>).<\/li>\n<\/ul>\n<\/li>\n\n
process292009b2ca8<\/code> (spid=\"87\"<\/code>).<\/li>\nprocess292009bb088<\/code> (spid=\"61\"<\/code>).<\/li>\n<\/ul>\n<\/li>\n<\/ol>\nmode=\"X\"<\/code> attribute indicates both processes are waiting for an exclusive lock.<\/p>\n5. Putting It All Together<\/h2>\n
\n
<victim-list><\/code> tag, look for which process was chosen to be rolled back.<\/li>\n<process-list><\/code> shows what each session was doing, including stored procedures, line numbers, and queries.<\/li>\n<resource-list><\/code> clarifies which locks each process holds and which they are requesting. Notice the cyclical dependency where one session holds a lock on one table while the other holds a lock on another table.<\/li>\n6. Best Practices to Avoid or Resolve Deadlocks<\/h2>\n
\n
Accessing the system_health Event Output<\/h2>\n
\n
\n
Final Thoughts<\/h2>\n
<deadlock><\/code> XML, you can troubleshoot and optimize your SQL Server environment for better concurrency and fewer unexpected rollbacks.<\/p>\n","protected":false},"excerpt":{"rendered":"