Different ways to Monitor Deadlocks

Every database environment, deadlocks are common problems and it is important for DBA’s to monitor deadlocks and make sure to prevent them from reoccurring.

There are multiple ways to monitor deadlocks

Using Trace Flags 

We can enable two trace flags 1204 and 1222 to capture deadlock information in error log. Run the below code, this will enable deadlock trace flags.

DBCC TRACEON (1204,1222);GO

If you would like to enable deadlock trace flags globally, you can use the below

DBCC TRACEON (1204,1222,-1);GO

1

Let’s force deadlock by creating two tables. Please run these queries in the order

--Session 1:
CREATE TABLE tbl_dlck1 (colum1 INT);GO
INSERT INTO tbl_dlck1 VALUES (1);GO
BEGIN TRAN
UPDATE tbl_dlck1 SET colum1 = 2;GO
-- Session 2 :
CREATE TABLE tbl_dlck2 (colum1 INT);
INSERT INTO tbl_dlck2 VALUES (1);GO
BEGIN TRAN
UPDATE tbl_dlck2 SET colum1 = 2;GO
--Session 1:
UPDATE tbl_dlck2 SET colum1 = 2;
--Session 2:
UPDATE tbl_dlck1 SET colum1 = 2;

2

Let’s see the error log. Here’s the deadlock information in the error log.

3.JPG

Using Profiler: 

I have selected “Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events in the profiler. Here is the result

4.png

Using Extended Events:

There are two ways we can capture deadlock information one is with system_health and another one is to create own extended event session. System_Health is default extended event which runs continuously by SQL Server.

Using System_Health extended event. Use the below queries to get the information.

select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health' 

Using XPath we can then get just the deadlock graphs from the XML by using a derived table and a CROSS APPLY to get the individual Event Nodes as follows:

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

This query will take long time to execute, but there is no server performance impact but you would expect this query to take more time.

5

Using creating own extended session to capture the deadlock. Select xml_deadlock_report event as shown below

6

7.png

That’s all about deadlocks.

References:

http://www.sqlservercentral.com/articles/deadlocks/65658/

https://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

Advertisements

About Ramasankar

My name is Ramasankar Molleti, and I have been working on sql server over 10 years, primarily focusing on Database Administration, Architect , Development and Business intelligence of sql server. I also hold various Microsoft certifications (Microsoft Certified Information Technology Professional, Microsoft Certified Technology Specialist, and Microsoft Certified Solution Associate). Disclaimer The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights. The owner of https://sqlram.wordpress.com will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
This entry was posted in Database Administration, Deadlock, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s