NoProfilerIn my last post, I shared a script that will take a running trace and show you the XE events that it relates to, and what columns are available within those XE events. Specifically, this was for converting a deadlock trace into an XE session; however the process is the same for converting any trace into an XE session. In today’s post, we’ll compare the deadlock trace and the new XE by running both, creating a deadlock, and comparing the captured data. We’ll look at the data captured in the XE from both script and GUI, and look at a few other differences between running a trace and an XE session.

The first step is to grab the trace and XE scripts from the prior post at http://www.sqlsolutionsgroup.com/wean-off-sql-profiler-part-1/. Modify both scripts to put the output files in an appropriate place on your system. Run both of the scripts to start the trace and to create the XE session. Next, start the XE session with the following script:

ALTER EVENT SESSION Deadlocks
ON SERVER
STATE = START;

 

The next step is to create a deadlock. Open up a new query window, and run the following. Leave this query window open.

USE tempdb;
GO
IF OBJECT_ID('dbo.Test1') IS NOT NULL DROP TABLE dbo.Test1;
IF OBJECT_ID('dbo.Test2') IS NOT NULL DROP TABLE dbo.Test2;
CREATE TABLE dbo.Test1 (col1 INT);
CREATE TABLE dbo.Test2 (col2 INT);
INSERT INTO dbo.Test1 VALUES (1),(2),(3),(4),(5);
INSERT INTO dbo.Test2 VALUES (1),(2),(3),(4),(5);
GO
BEGIN TRANSACTION
UPDATE dbo.Test1 SET col1 = col1*10 WHERE col1=3;

Next, open up a second query window, and run the following code in that window:

USE tempdb;
BEGIN TRANSACTION;
UPDATE dbo.Test2 SET col2 = col2*20 WHERE col2 = 4;
UPDATE dbo.Test1 SET col1 = col1*20 WHERE col1 = 3;
COMMIT TRANSACTION;

Finally, return to the first query window and run the following code, at which point one of the statements in one of the query windows will be deadlocked:

UPDATE dbo.Test2 SET col2 = col2*10 WHERE col2 = 4;
COMMIT TRANSACTION;
Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now that we’ve created a deadlock, let’s compare the trace output data to the XE output data. First, let’s grab the data from the trace file with this script, which selects all of the non-null columns from the table valued function (remember to change the filename/path as appropriate):

SELECT t2.TextData, t2.BinaryData, t2.NTUserName, t2.ClientProcessID, t2.ApplicationName,
       t2.LoginName, t2.SPID, t2.Duration, t2.StartTime, t2.EndTime, t2.ObjectID,
       t2.ServerName, t2.EventClass, t2.Mode, t2.DatabaseName, t2.Type
FROM   sys.traces t1
CROSS APPLY sys.fn_trace_gettable (path, NULL) t2
WHERE t1.path LIKE 'C:\SQL\Traces\%'
SSMS2014XE09-XE-Collected Trace Data

SSMS2014XE10-XE-Collected Trace Data

SSMS2014XE11-XE-Collected Trace Data

You can read the rest of this article over here.