Viewing Deadlock Graphs With The system_health Session

Jes Borland shows how you can avoid using trace flags 1204 and 1222 and view deadlocks from the system_health Extended Event:

This one isn’t bad, but imagine a multi-statement deadlock, or a server with several deadlocks in an hour – how do you easily see if there were other errors on the server at the same time?

With SQL Server 2012+, we have a better tool to see when deadlocks occur – and the deadlock graphs are saved by default, so we don’t have to read the text version to figure it out, or run a separate trace to capture them.

In SSMS, open Object Explorer and navigate to Extended Events > Sessions > system_health > package0.event_file. Double-click to view the data.

Click through for the entire process.

Related Posts

Defining Intent Locks

David Fowler explains what an intent lock is and why it’s useful: Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row. Now what’s […]

Read More

Extended Events Files on Linux

Jason Brimhall looks at an error when trying to set up an Extended Events session on Linux: This will fail before the query really even gets out of the gate. Why? The proc xp_create_subdir cannot create the directory because it requires elevated permissions. The fix for that is easy enough – grant permissions to write to […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30