How do we collect deadlocked queries via Extended Event and how do we read its data

Author
Recent Posts

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

certs

Latest posts by Stratos Matzouranis (see all)

How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

In older article we had seen how we create Extended Event to see long-running queries. In this article we will build Extended Event that records the deadlocks.

All we need to do is create it with a T-SQL command. After it is created, it will record in the path that we have declared to it in an XML file all the queries that lasted more than 1 second.

To create the Extended Event

We must not forget to change the path to the one where we want the data to be stored.

Then we execute it in a simple query window.

CREATE EVENT SESSION [deadLock] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_current_thread_id,package0.collect_system_time,package0.event_sequence,package0.last_error,package0.process_id,sqlos.cpu_id,sqlos.scheduler_id,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.query_plan_hash,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_frame,sqlserver.username)) 
ADD TARGET package0.event_file(SET filename=N'D:\xevents\deadlock.xel',max_file_size=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

alter event session deadLock on server state=start;
go

How do we read the results?

In the path we defined during its creation XEvent a .xel and a .xem file were created. Through the dynamic view sys.fn_xe_file_target_read_file with parameters these files we result in all the XML file with the information.

But since it is impractical to read an XML by eye, the file must somehow be filtered into a more readable form.

So I sat down and made a query that does this job

For starters, through the name we created, Xevent finds the path that is dynamically located and defines it as a parameter in the query.

Then we define the XML as one Common Table Expression. By doing SELECT we can get the value from each property we need.

In the where statement we can filter specific bases, the duration and the period of time it was executed.

This part needs attention, as the default xml timestamp is in UTC timezone, so to put the real time that the server has, we add the time difference that the server has with the UTC timezone (DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() )).

declare

@xel as varchar(max),
@xem as varchar(max) 

set @xel=(select 
--n.name as XeventName,
SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel
--,SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xem' as xem
from sys.server_event_session_fields f
inner join sys.server_event_sessions n on f.event_session_id = n.event_session_id
WHERE f.NAME = 'filename' 
and n.name like '%deadLock%') --Set xevent Name


set @xem=(select 
--n.name as XeventName,
--SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel
SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xem' as xem
from sys.server_event_session_fields f
inner join sys.server_event_sessions n on f.event_session_id = n.event_session_id
WHERE f.NAME = 'filename' 
and n.name like '%deadLock%')  --Set xevent Name

;WITH XEvents AS
(
select object_name, CAST(event_data AS XML) AS A FROM sys.fn_xe_file_target_read_file(
@xel
,@xem
, NULL, NULL)
)
SELECT 
A.value ('(/event/action[@name=''database_name'']/value)[1]', 'VARCHAR(MAX)') AS DB_Name,
DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(/event/@timestamp)[1]', 'DATETIME')) AS [Time],
d.name as DB_Name,

A.value ('(/event/data/value/deadlock/resource-list/objectlock/@objectname)[1]',  'VARCHAR(MAX)') AS locked_objectname,
A.value ('(/event/data[@name=''xml_report'']/value)[1]',  'VARCHAR(MAX)') AS All_Queries,
A.value ('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]',  'VARCHAR(MAX)') AS VictimProcess1,
A.value ('(/event/data/value/deadlock/victim-list/victimProcess/@id)[2]',  'VARCHAR(MAX)') AS VictimProcess2,
A.value ('(/event/data/value/deadlock/victim-list/victimProcess/@id)[3]',  'VARCHAR(MAX)') AS VictimProcess3,
A.value ('(/event/data/value/deadlock/victim-list/victimProcess/@id)[4]',  'VARCHAR(MAX)') AS VictimProcess4,
A.value ('(/event/data/value/deadlock/victim-list/victimProcess/@id)[5]',  'VARCHAR(MAX)') AS VictimProcess5,
A.value ('(/event/data/value/deadlock/process-list/process/@id)[1]',  'VARCHAR(MAX)') AS ListProcess1,
A.value ('(/event/data/value/deadlock/process-list/process/inputbuf)[1]',  'VARCHAR(MAX)') AS ListQuery1,
A.value ('(/event/data/value/deadlock/process-list/process/@hostname)[1]',  'VARCHAR(MAX)') AS hostname1,
A.value ('(/event/data/value/deadlock/process-list/process/@lockMode)[1]',  'VARCHAR(MAX)') AS lockMode1,
A.value ('(/event/data/value/deadlock/process-list/process/@isolationlevel)[1]',  'VARCHAR(MAX)') AS isolationlevel1,

A.value ('(/event/data/value/deadlock/process-list/process/executionStack/frame/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle1,
sf1.text as full_text1,
qp1.query_plan as query_plan1,

A.value ('(/event/data/value/deadlock/process-list/process/@id)[2]',  'VARCHAR(MAX)') AS ListProcess2,
A.value ('(/event/data/value/deadlock/process-list/process/inputbuf)[2]',  'VARCHAR(MAX)') AS ListQuery2,
A.value ('(/event/data/value/deadlock/process-list/process/@hostname)[2]',  'VARCHAR(MAX)') AS hostname2,
A.value ('(/event/data/value/deadlock/process-list/process/@isolationlevel)[2]',  'VARCHAR(MAX)') AS isolationlevel12,
A.value ('(/event/data/value/deadlock/process-list/process/@lockMode)[2]',  'VARCHAR(MAX)') AS lockMode2,
A.value ('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle2,
sf2.text as full_text2,
qp2.query_plan as query_plan2,
A.value ('(/event/data/value/deadlock/process-list/process/@id)[3]',  'VARCHAR(MAX)') AS ListProcess3,
A.value ('(/event/data/value/deadlock/process-list/process/inputbuf)[3]',  'VARCHAR(MAX)') AS ListQuery3,
A.value ('(/event/data/value/deadlock/process-list/process/@lockMode)[3]',  'VARCHAR(MAX)') AS lockMode3,
A.value ('(/event/data/value/deadlock/process-list/process/@hostname)[3]',  'VARCHAR(MAX)') AS hostname3,
A.value ('(/event/data/value/deadlock/process-list/process/@isolationlevel)[3]',  'VARCHAR(MAX)') AS isolationlevel3,
A.value ('(/event/data/value/deadlock/process-list/process[3]/executionStack/frame/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle3,
sf3.text as full_text3,
qp3.query_plan as query_plan3,
A.value ('(/event/data/value/deadlock/process-list/process/@id)[4]',  'VARCHAR(MAX)') AS ListProcess4,
A.value ('(/event/data/value/deadlock/process-list/process/inputbuf)[4]',  'VARCHAR(MAX)') AS ListQuery4,
A.value ('(/event/data/value/deadlock/process-list/process/@lockMode)[4]',  'VARCHAR(MAX)') AS lockMode4,
A.value ('(/event/data/value/deadlock/process-list/process/@hostname)[4]',  'VARCHAR(MAX)') AS hostname4,
A.value ('(/event/data/value/deadlock/process-list/process/@isolationlevel)[4]',  'VARCHAR(MAX)') AS isolationlevel4,
A.value ('(/event/data/value/deadlock/process-list/process[4]/executionStack/frame/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle4,
sf4.text as full_text4,
qp4.query_plan as query_plan4,
A.value ('(/event/data/value/deadlock/process-list/process/@id)[5]',  'VARCHAR(MAX)') AS ListProcess5,
A.value ('(/event/data/value/deadlock/process-list/process/inputbuf)[5]',  'VARCHAR(MAX)') AS ListQuery5,
A.value ('(/event/data/value/deadlock/process-list/process/@lockMode)[5]',  'VARCHAR(MAX)') AS lockMode5,
A.value ('(/event/data/value/deadlock/process-list/process/@hostname)[5]',  'VARCHAR(MAX)') AS hostname5,
A.value ('(/event/data/value/deadlock/process-list/process/@isolationlevel)[5]',  'VARCHAR(MAX)') AS isolationlevel5,
A.value ('(/event/data/value/deadlock/process-list/process[5]/executionStack/frame/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle5,
sf5.text as full_text5,
qp5.query_plan as query_plan5,
A.value ('(/event/data/value/deadlock/process-list/process/@id)[6]',  'VARCHAR(MAX)') AS ListProcess6,
A.value ('(/event/data/value/deadlock/process-list/process/inputbuf)[6]',  'VARCHAR(MAX)') AS ListQuery6,
A.value ('(/event/data/value/deadlock/process-list/process/@lockMode)[6]',  'VARCHAR(MAX)') AS lockMode6,
A.value ('(/event/data/value/deadlock/process-list/process/@hostname)[6]',  'VARCHAR(MAX)') AS hostname6,
A.value ('(/event/data/value/deadlock/process-list/process/@isolationlevel)[6]',  'VARCHAR(MAX)') AS isolationlevel6,
A.value ('(/event/data/value/deadlock/process-list/process[6]/executionStack/frame/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle6,
sf6.text as full_text6,
qp6.query_plan as query_plan6,
A.value ('(/event/data/value/deadlock/process-list/process/@id)[7]',  'VARCHAR(MAX)') AS ListProcess7,
A.value ('(/event/data/value/deadlock/process-list/process/inputbuf)[7]',  'VARCHAR(MAX)') AS ListQuery7,
A.value ('(/event/data/value/deadlock/process-list/process/@lockMode)[7]',  'VARCHAR(MAX)') AS lockMode7,
A.value ('(/event/data/value/deadlock/process-list/process/@hostname)[7]',  'VARCHAR(MAX)') AS hostname7,
A.value ('(/event/data/value/deadlock/process-list/process/@isolationlevel)[7]',  'VARCHAR(MAX)') AS isolationlevel7,
A.value ('(/event/data/value/deadlock/process-list/process[7]/executionStack/frame/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle7,
sf7.text as full_text7,
qp7.query_plan as query_plan7,
A   AS xml_report
--,SUBSTRING(CAST(A as varchar(max)), CHARINDEX('<inputbuf>', CAST(A as varchar(max))), CHARINDEX('</inputbuf>',CAST(A as varchar(max))) - CHARINDEX('<inputbuf>', CAST(A as varchar(max))) + Len('</inputbuf>')) as VictimQuery,
--SUBSTRING(CAST(A as varchar(max)), CHARINDEX('<inputbuf>',CAST(A as varchar(max)),CHARINDEX('<inputbuf>',CAST(A as varchar(max)))+10), CHARINDEX('</inputbuf>',CAST(A as varchar(max)),CHARINDEX('</inputbuf>',CAST(A as varchar(max)))+10) - CHARINDEX('<inputbuf>',CAST(A as varchar(max)),CHARINDEX('<inputbuf>',CAST(A as varchar(max)))+1) + Len('</inputbuf>')) as TheOtherQuery
From XEvents x
left join sys.databases d on x.A.value ('(/event/data/value/deadlock/resource-list/objectlock/@dbid)[1]',  'VARCHAR(MAX)') =d.database_id
--left join  sys.dm_exec_query_stats qs1 on  qs1.sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1) 
outer apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf1
outer apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp1

outer apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf2
outer apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp2

outer apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[3]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf3
outer apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[3]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp3

outer apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[4]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf4
outer apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[4]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp4

outer apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[5]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf5
outer apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[5]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp5

outer apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[6]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf6
outer apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[6]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp6

outer apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[7]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf7
outer apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(/event/data/value/deadlock/process-list/process[7]/executionStack/frame/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp7

where  1=1
and DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(/event/@timestamp)[1]', 'DATETIME'))  between '2018-01-26 10:26:00.000' and '2018-12-26 10:28:00.000'
--and d.name ='DB_1'

The result

The result of the query brought us for this period of time to the base where we asked him for the queries that were deadlocked, the list of queries along with the process id, the locking mode, the isolation level that each was running and finally what were the Victim that is, which were killed so that the rest could continue.

How we collect deadlocked queries via Extended Event and how we read its data

Sources:

Quickstart: Extended Events in SQL Server

Share it

Cookie	Duration	Description
cookielawinfo-checkbox-advertisement	1 year	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Advertisement".
cookielawinfo-checkbox-analytics	11 months	This cookie is set by the GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the "Analytics" category.
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the "Functional" category.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by the GDPR Cookie Consent plugin. The cookies are used to store the user consent for the cookies in the "Necessary" category.
cookielawinfo-checkbox-others	11 months	This cookie is set by the GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by the GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the "Performance" category.
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not the user has consented to the use of cookies. It does not store any personal data.

Cookie	Duration	Description
__gads	1 year 24 days	This cookie is set by Google and stored under the name dounleclick.com. This cookie is used to track how many times users see a particular advert which helps in measuring the success of the campaign and calculate the revenue generated by the campaign. These cookies can only be read from the domain that it is set on so it will not track any data while browsing through other sites.
_ga	2 years	This cookie is installed by Google Analytics. The cookie is used to calculate visitor, session, campaign data and keep track of site usage for the site's analytics report. The cookies store information anonymously and assign a randomly generated number to identify unique visitors.
_gid	1 day	This cookie is installed by Google Analytics. The cookie is used to store information of how visitors use a website and helps in creating an analytics report of how the website is doing. The data collected including the number of visitors, the source where they came from, and the pages visited in an anonymous form.

Cookie	Duration	Description
IDE	1 year 24 days	Used by Google DoubleClick and stores information about how the user uses the website and any other advertisement before visiting the website. This is used to present users with ads that are relevant to them according to the user profile.
test_cookie	15 minutes	This cookie is set by doubleclick.net. The purpose of the cookie is to determine if the user's browser supports cookies.

To create the Extended Event

How do we read the results?

The result

Sources:

Leave a reply Cancel reply