When troubleshooting a slow query or a specific application session, the instance-level wait statistics in sys.dm_os_wait_stats are too coarse: they aggregate wait events from all processes since the last restart. Resetting the view with DBCC SQLPERF is also problematic on busy instances where hundreds of other sessions generate noise.

SQL Server 2008’s Extended Events framework solves this elegantly: you can create a lightweight, filtered event session that collects wait statistics for a single SPID, with negligible overhead on the rest of the instance.

The Procedure: Step by Step

The workflow is: create the XE session filtered to your target SPID → start it → run your query or wait for the session to accumulate data → stop it → read and analyze the collected file.

Prerequisites: the account running this procedure needs ALTER ANY EVENT SESSION permission. Collected data is written to disk — monitor the output folder for space usage during long collection windows.

-- 1) Drop the monitor session if it already exists
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'WaitMon')
    DROP EVENT SESSION WaitMon ON SERVER
GO

-- 2) Create the filtered session
--    Replace 79 with the actual session_id to monitor
--    Replace the file paths with a valid location on your server
CREATE EVENT SESSION WaitMon ON SERVER
ADD EVENT sqlos.wait_info
    (WHERE sqlserver.session_id = 79)
ADD TARGET package0.asynchronous_file_target
    (SET FILENAME     = N'S:	empEE_WaitMonStats.xel',
         METADATAFILE = N'S:	empEE_WaitMonStats.xem')
WITH (max_dispatch_latency = 1 SECONDS)
GO

-- 3) Start collecting
ALTER EVENT SESSION WaitMon ON SERVER STATE = START
GO

-- 4) >>> Run your query or wait for the session under analysis to execute <<<

-- 5) Stop collecting
ALTER EVENT SESSION WaitMon ON SERVER STATE = STOP
GO

-- 6) Load collected data into a temp table
CREATE TABLE #RawEventData (
    Rowid      INT IDENTITY PRIMARY KEY,
    event_data XML
)

INSERT INTO #RawEventData (event_data)
SELECT CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file(
    'S:	empEE_WaitMonStats*.xel',
    'S:	empEE_WaitMonStats*.xem',
    NULL, NULL
)
GO

-- 7) Analyze wait events by type
SELECT
    waits.[Wait Type],
    COUNT(*)                                                    AS [Wait Count],
    SUM(waits.[Duration])                                       AS [Total Wait ms],
    SUM(waits.[Duration]) - SUM(waits.[Signal Duration])        AS [Resource Wait ms],
    SUM(waits.[Signal Duration])                                AS [Signal Wait 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
) waits
WHERE waits.[Op] = 'End'
GROUP BY waits.[Wait Type]
ORDER BY [Total Wait ms] DESC
GO

-- 8) Clean up
DROP TABLE #RawEventData
DROP EVENT SESSION WaitMon ON SERVER

The results show exactly which wait types are consuming time for your target session — I/O waits, lock waits, memory waits, network I/O, and so on — with the total accumulated wait time and the split between resource waits (actual waits for the resource) and signal waits (time spent in the runnable queue after the resource became available).