Querying SQL Server Extended Events for Deadlock history.
Extended events capture a lot of data from the system and you should explore that as well. There have a lot of useful detailed information which is already being captured. Please have a look at this event on MSDN here.
We are only concerned about the deadlocks. The deadlocks are also captured in the system trace extended event with the object name (a column in the system trace) as “xml_deadlock_report”. So, we will be querying a system view “sys.fn_xe_file_target_read_file” to query the extended event trace “system_health” which is already running by default so you don’t have to setup anything. You can get detailed information about the system view “sys.fn_xe_file_target_read_file” from MSDN here.
We get the deadlock graph which is by default in an XML Format. After that, we need to parse the XML so that we can read it easily. There is a Microsoft SQL Server Stored Procedure for that and it’s pretty handy, i.e. “sp_xml_preparedocument”.
We will parse the document using the system stored procedure and will get the following columns from the deadlock graph XML. The columns which we be getting will be under the following node so we start reading XML from that particular node “event/data/value/deadlock/process-list/process”.
Columns read from the XML Deadlock:
- Id
The Process ID which is required to uniquely identify each process. - Hostname
Hostname is the machine name of the application which is requesting the query. - Loginname
Login name is the SQL/Windows authentication login which is authenticated to initiate the session. - Clientapp
Client Application like dot net framework application or SQL Server Management studio etc. - Inputbuf & executionStack/frame
Above two columns get the actual TSQL executed by the sessions. This is the most important part as it will have all the actual details in it. - Lastbatchcompleted
The time of the deadlock is captured in this column. This is also important for logging and analysis purpose.
There is one variable “@GetDeadLocksForLastMinutes”, which you can configure as per your requirements, which is to set as a threshold of time (in minutes) for the deadlock script to watch for events back in history. The script then captures all the information from the XML and puts it into a Temporary Table and loops through all the Deadlocks which might have occurred in the timeframe mentioned. At the end it will just display all the information captured in it using simple columns. All the result set columns are discussed in this article later.
/*
Declaration of the variables
#DeadLockXMLData to store each Dead lock XML from the extended Event
#DeadLockDetails to store deadlock process, victim and application information
@GetDeadLocksForLastMinutes For how many number of Minutes to watch for
*/
SET NOCOUNT ON;
CREATE TABLE #DeadLockXMLData(DeadLockXMLData XML,DeadLockNumber INT)
CREATE TABLE #DeadLockDetails(ProcessID nVARCHAR(50),HostName nVARCHAR(50),LoginName nVARCHAR(100)
,ClientApp nVARCHAR(100), Frame nVARCHAR(MAX),TSQLString nVARCHAR(MAX),DeadLockDateTime DATETIME,IsVictim TINYINT,DeadLockNumber INT)
DECLARE @DeadLockXMLData AS XML,@DeadLockNumber INT,@getInputBuffer CURSOR,@Document AS INT, @SQLString NVARCHAR (MAX),@GetDeadLocksForLastMinutes INT;
SET @GetDeadLocksForLastMinutes = 60;
/*INSERT THE DEADLOCKS FROM EXTENDED EVENTS TO TEMP TABLES & FILTER ONLY DEADLOCKS*/
INSERT INTO #DeadLockXMLData(DeadLockXMLData,DeadLockNumber)
SELECT CONVERT(XML, event_data) DeadLockXMLData,ROW_NUMBER() OVER (ORDER BY Object_name) DeadLockNumber
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME = 'xml_deadlock_report'
/*START A CURSOR TO LOOP THROUGH ALL THE DEADLOCKS AS YOU MIGHT GET MUTLTIPLE DEADLOCK IN PRODUCTION AND YOU WOULD WANT ALL OF THEM*/
SET @getInputBuffer = CURSOR FOR
SELECT DeadLockXMLData,DeadLockNumber FROM #DeadLockXMLData
OPEN @getInputBuffer
FETCH NEXT
FROM @getInputBuffer INTO @DeadLockXMLData,@DeadLockNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Document = 0
SET @SQLString = ''
EXEC sp_xml_preparedocument @Document OUTPUT, @DeadLockXMLData
/*INSERT PARSED DOCUMENT'S DATA FROM XML TO TEMP TABLE FOR READABILITY*/
INSERT INTO #DeadLockDetails(ProcessID,HostName,LoginName,ClientApp,Frame,TSQLString,DeadLockDateTime,DeadLockNumber)
SELECT ProcessID, HostName,LoginName,ClientApp, Frame,TSQL AS TSQLString,LastBatchCompleted,@DeadLockNumber
FROM OPENXML(@Document, 'event/data/value/deadlock/process-list/process')
WITH
(
ProcessID [varchar](50) '@id',
HostName [varchar](50) '@hostname',
LoginName [varchar](50) '@loginname',
ClientApp [varchar](50) '@clientapp',
CustomerName [varchar](100) '@clientapp',
TSQL [nvarchar](4000) 'inputbuf',
Frame nVARCHAR(4000) 'executionStack/frame',
LastBatchCompleted nVARCHAR(50) '@lastbatchcompleted'
)
/*UPDATE THE VICTIM SPID TO HIGHLIGHT TWO QUERIES SEPARETELY, THE PROCESS (WHO CREATED THE DEADLOCK) AND THE VICTIM*/
UPDATE #DeadLockDetails
SET IsVictim = 1
WHERE ProcessID IN (
SELECT ProcessID
FROM OPENXML(@Document, 'event/data/value/deadlock/victim-list/victimProcess')
WITH
(
ProcessID [varchar](50) '@id',
HostName [varchar](50) '@hostname',
LoginName [varchar](50) '@loginname',
ClientApp [varchar](50) '@clientapp',
CustomerName [varchar](100) '@clientapp',
TSQL [nvarchar](4000) 'inputbuf',
Frame nVARCHAR(4000) 'executionStack/frame',
LastBatchCompleted nVARCHAR(50) '@lastbatchcompleted'
)
)
EXEC sp_xml_removedocument @Document
FETCH NEXT
FROM @getInputBuffer INTO @DeadLockXMLData,@DeadLockNumber
END
CLOSE @getInputBuffer
DEALLOCATE @getInputBuffer
SELECT *
FROM #DeadLockDetails D
WHERE DATEDIFF(MINUTE,DeadLockDateTime,GETDATE()) <= @GetDeadLocksForLastMinutes
AND IsVictim = 1
ORDER BY DeadLockNumber
DROP TABLE #DeadLockXMLData,#DeadLockDetailsThe columns of the table are mentioned below for your reference and explanation:
- DeadLockDateTime
This is the time when the actual deadlock happened. The trace might take couple of minutes to populate the deadlock but the time will be accurate. - HostName
The machine name which is accessing the data. This is the application server or the client application machine name. This is important to identify the system which is trying to access the data. - LoginName
The SQL/Windows authenticated login name which is used to access the database. This is critical to identify which login is being used to access the data. - ClientApp
The client application name will be provided in this column. It will be like SQL Server Management Studio or the dot net framework application or whichever application you are using to get the data to the user. - VictimTSQL
This is the TSQL for the session which was chosen as the deadlock victim and didn’t complete its processing. - ProcessTSQL
This is the TSQL for the process which was executed successfully but caused the deadlock to happen.
In the table above the most important columns are the Victim and Process TSQL along with the Application name. After executing and getting all the information about the deadlock, now you can do further analysis and fix the problem.
Comments