Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

SQL Server – SSMS - Profiler - Extracting Deadlock Event Data to XDL files

Oct 30 2011 9:00PM by Nakul Vachhrajani   

A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. We can use the SQL Server Profiler to capture deadlock information. Today, we will see how to use the SQL Server Profiler to capture deadlock information, and also how to export this information for distribution to the development team.

Setting up the Environment

First of all, let’s create a scenario that would create the necessary test data for us to generate a deadlock. In the query below, you will find 3 sets of transaction-bound queries, which need to be executed in 3 different SSMS windows – all connected to the same SQL Server instance.

/*
WARNING: This script is provided "as-is" and without warranty.
         Please do not use this in quality assurance and/or production environments.
*/

-- Step 01: To be run in query window #1
USE tempdb
GO

CREATE TABLE DeadlockTestTable1 (DL1Id   INT, DL1Name VARCHAR(20))
CREATE TABLE DeadlockTestTable2 (DL2Id   INT, DL2Name VARCHAR(20))

INSERT INTO DeadlockTestTable1 VALUES (1, 'Deadlock'), (2, 'SQL Server'), (3, 'Test')
INSERT INTO DeadlockTestTable2 VALUES (1, 'Deadlock'), (2, 'SQL Server'), (3, 'Test')

-- Step 02: To be run in query window #1
USE tempdb
GO
BEGIN TRANSACTION 

UPDATE DeadlockTestTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2
WAITFOR DELAY '00:00:20'
UPDATE DeadlockTestTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2

ROLLBACK TRANSACTION

-- Step 03: To be run in query window #2
USE tempdb
GO
BEGIN TRANSACTION 

UPDATE DeadlockTestTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2
WAITFOR DELAY '00:00:20'
UPDATE DeadlockTestTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2

ROLLBACK TRANSACTION

-- Step 04: To be run in query window #3
USE tempdb
GO
BEGIN TRANSACTION 

UPDATE DeadlockTestTable2 SET DL2Name = 'Uplock' WHERE DL2Id = 2
WAITFOR DELAY '00:00:10'
UPDATE DeadlockTestTable1 SET DL1Name = 'Downlock' WHERE DL1Id = 2

ROLLBACK TRANSACTION

Using the Profiler to Capture & Export the Deadlock trace

In this section, we will see how to use the SQL Server profiler to capture the deadlock trace.

  1. Launch the SQL Server profiler and connect to the desired SQL Server instance
  2. Start a new trace
  3. Under Event selection, clear out any default events. Only keep the Locks –> “deadlock graph” event class as checked. You may also have “Lock:Deadlock” and “Lock:Deadlock chain” as checked
  4. image
  5. Once the trace is running, in SSMS Query window #1, run the query intended for window #1
  6. In SSMS Query window #2, run the query intended for window #2. Repeat this process for query #3
  7. Within 20 seconds, two of the 3 queries would have failed execution because they would have been chosen as the deadlock victims
  8. The Profiler trace would look like the following:
  9. image
  10. One can analyze the deadlock graph here itself. In case this deadlock needs to be sent over to the development team, one can follow one of the many steps highlighted below

Export Deadlock Data – Export Selected deadlock information only

If only a selected deadlock needs to be forwarded to the development team for analysis, then, simply right-click on the deadlock and choose “Extract Event Data”. Deadlock graph information can then be saved to any desired location of your choice. The deadlock graph information is stored in the form of SQL Server Deadlock XML files (*.xdl). As the name indicates, it is ultimately an XML file containing the XML representation of the deadlock graph.

image

Export Deadlock Data – Export all deadlock information

If you need to extract all deadlock graphs from a SQL Server profiler trace, go to File->Export->Extract SQL Server events->Extract Deadlock events…

image

Export this information to a location of your choice in the form of SQL Server Deadlock XML files (*.xdl). You may choose to save all events in a single file, or have separate files for each deadlock event.

image

Opening the trace in SQL Server Management Studio

Now that the deadlock information has been exported to the file system, you can now E-mail these files over to the development team who can in-turn analyze the deadlock graphs for you, and arrive at a resolution to the deadlock issue.

To open these files, one does not need to use the SQL Profiler. The Management Studio (SSMS) is capable of opening the XDL files and representing the information as a deadlock graph.

image

image

As you can see from the image above, hovering the mouse over the deadlock victim also reveals the affected query. You may also open the XDL file in an XML editor of your choice, if you would like to see the deadlock information in the XML format.

I hope that the above method will help you quickly & efficiently exchange deadlock data & related analysis with your development team.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Tags: Development, Administration, T-SQL, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, DBA, SSMS, Tools and Utilities, Debugging,


Nakul Vachhrajani
4 · 36% · 11645
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

6  Comments  

  • Thanks Nakul for nice post

    commented on Oct 31 2011 1:24AM
    Hardik Doshi
    20 · 9% · 2864
  • Sounds great Nakul,Thanks for sharing this...

    commented on Nov 1 2011 5:47PM
    Robert Dennyson
    11 · 14% · 4430
  • Well explained post...I always wanted to see posts on SQL profiler that detail how to use it...Can anyone point me to good posts on how to use Profiler for performance issues?

    commented on Nov 11 2011 8:31AM
    vanne040
    84 · 2% · 657
  • @vanne040: You got it. I will write up a couple of posts to get you started, and point you to other posts which I may encounter during my research on the topic. Stay tuned - I will post the URL of the 1st part of the series on this post.

    commented on Nov 12 2011 12:41AM
    Nakul Vachhrajani
    4 · 36% · 11645
  • Nice write-up Nakul. Thanks. Appreciate your time. I am eager to read and learn more on Profiler.

    commented on Nov 14 2011 8:56AM
    vanne040
    84 · 2% · 657

Your Comment


Sign Up or Login to post a comment.

"SQL Server – SSMS - Profiler - Extracting Deadlock Event Data to XDL files" rated 5 out of 5 by 3 readers
SQL Server – SSMS - Profiler - Extracting Deadlock Event Data to XDL files , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]