SQL SERVER – Finding Waits For Any Session with Extended Event wait_info

One of the most sought after blog is around Wait Stats when it comes to performance troubleshooting. The place to bookmark would be – SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28. I get queries from a number of you and have been fortunate in the past for sending me such information because it becomes a great learning experience for me too. At the same time, your problems are getting solved too. It becomes a great win-win situation. Let us learn about Extended Event wait_info in this blog post.

In a recent troubleshooting exercise, one of the senior DBA inside a company asked me how to understand and know the waits related to a single session or a query they are running. This was a great challenge and I sent them a script a week later. I thought it is also worth to share here as part of the blog for future reference.

I wondered where to start and realized the best way to find this information is used Extended Events. Here is the script that I came up with:

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. All the waits have been actively captured in an. xel file and we can use SQL Server Management Studio to watch the waits that happened to our Session ID 52 as per the script above.

If you want to look at specific Wait types, we can use the map codes for the Wait Stats using the DMV as shown below for PAGEIO codes:

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

Do you think this script will be useful for you? Are there specific scenarios you will be using such code? Do let me know via comments and would love to hear from your experiences too. Extended Event wait_info

Reference: Pinal Dave (https://blog.sqlauthority.com), Twitter