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

Delete command from Distribution Database

  1. Identify the publisher database id
    SELECT * FROM MSpublisher_databases
  2. Identify the ID number and command id of the command causing the problem. This will typically show up in the Replication Monitor.
  3. Execte the sp_browsereplcmds stored procedure to identify the command with the problem.
    EXECUTE distribution.dbo.sp_browsereplcmds
        @xact_seqno_start = '0x0001DAB7000027C8000100000000',
        @xact_seqno_end = '0x0001DAB7000027C8000100000000',
        @publisher_database_id = 7,
        @command_id = 1
    
  4. Delete the command from MSRepl_commands using the xact_seqno and command_id
    DELETE FROM MSrepl_commands WHERE xact_seqno = 0x0001DAB7000027C8000100000000 AND command_id = 1
    
  5. Rinse and repeat as necessary.