Simple Extended events for a SSMS process

SQL Server generates wait stats. If we have a SSMS session open, we can capture the events associated with activity in that session by pushing them into an extended event XML file with nodes like this

<event name=”wait_info” package=”sqlos” timestamp=”2015-01-27T18:59:17.727Z”>
<data name=”wait_type”>
<value>124</value><text>SOS_SCHEDULER_YIELD</text></data>
<data name=”opcode”><value>1</value><text>End</text></data>
<data name=”duration”><value>4</value></data>
<data name=”signal_duration”><value>4</value></data>
</event>

First get the SPID of the session with

 select @@SPID.

Get any current event sessions with

SELECT * FROM sys.server_event_sessions

Create a session with a different name (or drop with the name you want if it shows up above)

CREATE EVENT SESSION myNAME ON SERVER
ADD EVENT sqlos.wait_info
 (WHERE sqlserver.session_id = 53 
    /* session_id of connection from select @@SPID */)
ADD TARGET package0.asynchronous_file_target
 (SET FILENAME = N'C:\EE_WaitStats.xel',  
  METADATAFILE = N'C:\EE_WaitStats.xem')
WITH (max_dispatch_latency = 1 seconds);
GO

start the session, run the query, end the session

ALTER EVENT SESSION myNAME ON SERVER STATE = START;
— query here
ALTER EVENT SESSION myNAME ON SERVER STATE = STOP;

Count the events

SELECT COUNT (*) FROM sys.fn_xe_file_target_read_file
 ('C:\SQLskills\EE_WaitStats*.xel',
 'C:\SQLskills\EE_WaitStats*.xem', null, null);
 
Output is 2xs the number of waits since there is an event on start wait and one on end wait.
 
The data is captured in the XML data files. Jonathan K has a script to pull out the data (note the wildcard in the name of theimport file)
CREATE TABLE #RawEventData (
 Rowid INT IDENTITY PRIMARY KEY, event_data XML);
 

– Read the file data into intermediate temp table
INSERT INTO #RawEventData (event_data)
SELECT
 CAST (event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file (
 'C:\EE_WaitStats*.xel',
 'C:\EE_WaitStats*.xem', null, null);

USE XQuery to pull the XML data from the temp file and display it on the display

SELECT
waits.[Wait Type],
COUNT (*) AS [Wait Count],
SUM (waits.[Duration]) AS [Total Wait Time (ms)],
SUM (waits.[Duration]) – SUM (waits.[Signal Duration])
AS [Total Resource Wait Time (ms)],
SUM (waits.[Signal Duration]) AS [Total Signal Wait Time (ms)]FROM
(SELECT event_data.value (‘(/event/@timestamp)[1]’, ‘DATETIME’) AS [Time],
event_data.value (‘(/event/data[@name=”wait_type”]/text)[1]’, ‘VARCHAR(100)’) AS [Wait Type],
event_data.value (‘(/event/data[@name=”opcode”]/text)[1]’, ‘VARCHAR(100)’) AS [Op],
event_data.value (‘(/event/data[@name=”duration”]/value)[1]’, ‘BIGINT’) AS [Duration],
event_data.value (‘(/event/data[@name=”signal_duration”]/value)[1]’, ‘BIGINT’) AS [Signal Duration] FROM #RawEventData
) AS waits
WHERE waits.[op] = ‘End’
GROUP BY waits.[Wait Type]
ORDER BY [Total Wait Time (ms)] DESC
– Cleanup
DROP TABLE #RawEventData;

output looks like this

Wait Type                           Wait    Total       Total         Total Signal Wait
Count   Wait       Res Wait       Wait (ms)
———————                ———– ——————– —————————– —————————
LATCH_EX                        4919     15783       15693                 90
LATCH_SH                             18     4399         4398                  1
CXPACKET                             16     3039         3038                  1
PAGEIOLATCH_SH           813        468          457                 11
PAGELATCH_SH               246         113          109                  4
WRITELOG                          99           71          66                    5
PAGELATCH_UP              203           65          63                     2
LOGBUFFER                     123           58          57                     1
WRITE_COMPLETION     82           55          55                    0
PAGEIOLATCH_UP           35           27           27                    0
IO_COMPLETION             80           25          25                    0
SQLTRACE_LOCK               1            6              6                    0
PAGEIOLATCH_EX             4          3              3                    0
CMEMTHREAD                 17            1              0                     1
SOS_SCHEDULER_YIELD 6         0              0                     0

Advertisements