Wait stats are an essential aspect of performance troubleshooting in SQL Server. They provide valuable insights into the bottlenecks and delays that occur during query execution. In a recent troubleshooting exercise, a senior DBA asked me how to understand and identify the waits related to a single session or query. This challenge led me to explore the use of Extended Events, and I want to share the solution with you in this blog post.

Extended Events is a powerful feature in SQL Server that allows you to capture and analyze detailed information about various events occurring within the database engine. By leveraging Extended Events, we can specifically target and capture wait stats related to a particular session or query.

Here is a script that demonstrates how to create an Extended Event session to capture wait stats for a single session:

CREATE EVENT SESSION WaitsForSingleSession ON SERVER
ADD EVENT sqlos.wait_info
(ACTION (sqlserver.sql_text)
WHERE sqlserver.session_id = 52 -- Change SESSION_ID that matches your environment
AND opcode = 1),
ADD EVENT sqlos.wait_info_external
(ACTION (sqlserver.sql_text)
WHERE sqlserver.session_id = 52 -- Change SESSION_ID that matches your environment
AND opcode = 1)
ADD TARGET package0.asynchronous_file_target
(SET FILENAME = N'c:\waitstats\Session-52-Waits.xel')
WITH (max_dispatch_latency = 1 seconds)
GO

ALTER EVENT SESSION WaitsForSingleSession ON SERVER STATE = START

-- Run the query now

-- After the extended event has been created, run the query in a separate session using SQL Server Management Studio or if you already know the session ID from somewhere – use the same and change the above script accordingly. Once you realize the session has ended, we can stop the Extended Event and clean up the script.

ALTER EVENT SESSION WaitsForSingleSession ON SERVER STATE = STOP
GO

DROP EVENT SESSION WaitsForSingleSession ON SERVER
GO

As you can see from the script, we create an Extended Event session named “WaitsForSingleSession” and add two events: “sqlos.wait_info” and “sqlos.wait_info_external”. These events capture wait stats for the specified session ID (52 in this example) and store them in an .xel file. You can change the session ID to match your environment.

To start capturing the wait stats, execute the script and run the query or session you want to monitor. Once the session has ended, stop the Extended Event session and clean up the script.

After capturing the wait stats, you can use SQL Server Management Studio to analyze the data. The captured waits will be stored in the specified .xel file. You can open the file in Management Studio and explore the waits that occurred during the monitored session.

If you want to focus on specific wait types, you can use the DMV (Dynamic Management View) “sys.dm_xe_map_values” to map the wait types to their corresponding codes. For example, to look at PAGEIO wait types, you can use the following query:

SELECT * FROM sys.dm_xe_map_values
WHERE name = 'wait_types'
AND (map_value LIKE '%PAGEIO%')

This query will provide you with the wait types related to PAGEIO operations.

I hope you find this script and approach useful for troubleshooting performance issues in your SQL Server environment. If you have any specific scenarios or experiences related to wait stats and Extended Events, please share them in the comments. I would love to hear from you!