Finding Deadlocks

Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks on Nov 20th, 2017 | 0 comments

Recently we experienced deadlocks on one of our staging servers, so I wanted to capture those deadlocks using Extended Events. This how I did it.

First of all you need location where you gonna keep your XEL file, then we can create Extended Event session

Using SSMS

Creating new Extended Event session

Selecting Events you want to capture, in this case xml_deadlock_report

Save the event data to an XEL file

Using T-SQL

CREATE EVENT SESSION [Deadlock_Report] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'E:\XE\Temp\XEventSessions\deadlock_report.xel',max_file_size=(10240))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=10 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Now we can start extended event session

/* Start  session */
ALTER EVENT SESSION [Deadlock_Report] ON SERVER
STATE = START;

I managed to replicate and capture deadlock event on my staging environment and this is how you read results from extended events.

Or we can query the file target directly using T-SQL and create deadlock_xml column so we can examine deadlock graph further

SELECT
xe.event_data,
xe.event_data.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_xml
FROM sys.fn_xe_file_target_read_file
(N'E:\XE\Temp\XEventSessions\deadlock_report*.xel',
N'E:\XE\Temp\XEventSessions\deadlock_report*.xem', null, null)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xe

If you have lots of events and you want to manipulate the XML values this can be really slow process. In that case you can dump the event data values into temporary table first.

In this example I am querying file target directly.

From here you can open deadlock_xml (deadlock graph) with SQL Sentry Explorer and analyze graph further

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / Extended Events / Finding Deadlocks

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud