Using Extended Events to Monitor Waits

Below is an Extended Events demo that shows you how to monitor the wait events on a specific SPID. You create test table, popluate it with some values, create the event session, start the session, run some kind of sql payload, stop the session and then view the data. The code has been tested on both SQL Server 2008 R2 and SQL Server 2012. This is a pretty cool method if you want to watch a specific stored procedure call and how many and what types of waits are generated.

--Create the table
CREATE TABLE TestTable (
col1 INT IDENTITY,
col2 UNIQUEIDENTIFIER DEFAULT NEWID(),
col3 CHAR(1000) DEFAULT 'ninjas');
CREATE CLUSTERED INDEX [IX_TestTable_c1] ON [TestTable] (col1);
GO

--Insert the 5000 Rows
SET NOCOUNT ON;
INSERT INTO TestTable DEFAULT VALUES;
GO 5000

--Delete the Event Session if it exists
IF EXISTS ( SELECT * from sys.server_event_sessions WHERE [name] = 'Monitor_Waits')
DROP EVENT SESSION Monitor_Waits ON SERVER


-- Create the Events session
-- Open a new query window, this is where you will run your sql payload.
-- First get the SPID Number of the new query window by running the SELECT @@SPID
-- Replace the XX with your SPID number
CREATE EVENT SESSION [Monitor_Waits] ON SERVER
ADD EVENT sqlos.wait_info
	(WHERE [sqlserver].session_id = 58)
ADD TARGET package0.asynchronous_file_target
	(SET FILENAME = 'C:\mssql\EE_WaitStats.xel', METADATAFILE = 'C:\mssql\EE_WaitStats.xem')
WITH (max_dispatch_latency = 1 seconds);


--Start the Event Session
ALTER EVENT SESSION Monitor_Waits ON SERVER STATE = START;


--Hop over to your other query window and execute you payload.
--Sample Payload
--Index Rebuild
--REBUILD Index after starting event session
--ALTER INDEX IX_TestTable_c1 ON TestTable REBUILD;
--MASS update
--UPDATE TestTable set col3 = 'neat'


--Stop the Event Session
ALTER EVENT SESSION Monitor_Waits ON SERVER STATE = STOP;

--Make sure you captured some data in your event file.
SELECT COUNT(*) 
FROM sys.fn_xe_file_target_read_file ('C:\mssql\EE_WaitStats*.xel','C:\mssql\EE_WaitStats*.xem',NULL,NULL)


--Create a temp table to store the results.
CREATE TABLE ##Raw_EventData (
Rowid INT IDENTITY PRIMARY KEY,
event_data XML);


--Insert the data from the event file to your temp table.
INSERT INTO ##Raw_EventData (event_data)
SELECT CAST(event_data as XML) as event_data
FROM sys.fn_xe_file_target_read_file ('C:\mssql\EE_WaitStats*.xel','C:\mssql\EE_WaitStats*.xem',NULL,NULL);


--Select all the events
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 (ms)],
event_data.value ('(/event/data[@name=''max_duration'']/value)[1]','BIGINT') as [Max_Duration (ms)],
event_data.value ('(/event/data[@name=''total_duration'']/value)[1]','BIGINT') as [Total_Duration (ms)],
event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]','BIGINT') as [Signal_Duration (ms)],
event_data.value ('(/event/data[@name=''completed_count'']/value)[1]','BIGINT') as [Count]
FROM ##Raw_EventData



--Aggregate the data
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 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 ##Raw_EventData) as waits
WHERE waits.op = 'End'
GROUP By waits.[Wait_Type]
ORDER BY [Total Wait Time (ms)] DESC



--Clean up
DROP TABLE ##Raw_EventData

IF EXISTS ( SELECT * from sys.server_event_sessions WHERE [name] = 'Monitor_Waits')
DROP EVENT SESSION Monitor_Waits ON SERVER

DROP TABLE TestTable
Advertisements

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