Wait Statistics on a Query

Wait statistics are a vital part of understanding what is causing your system to run slowly. Capturing them can be done through a variety of mechanisms from sys.dm_os_wait_stats (use this query for that) to sys.dm_db_wait_stats in Azure SQL Database. Those cover the system and the database, however, what about capturing query wait statistics on a specific query?

Query Wait Statistics

There was a time when this was actually kind of difficult. However, now we have a lot of different tools to capture query wait statistics. First up, and really, one of the best and easiest ways to deal with this, is to use the wait statistics captured by the Query Store. The only drawback to this method is that it is an aggregation of query wait statistics for the given query. Well, that and you have to be running Azure SQL Database or SQL Server 2017

Another mechanism to capture query wait statistics is to use an Actual Execution plan. This method is great in that it captures the query wait statistics for a single query. However, you have to capture an execution plan, which is not a free operation. Further, this only works for SQL Server 2016 or better or Azure SQL Database and then only when using SSMS 17.4.

Which leaves? Extended Events.

Query Wait Statistics in Extended Events

Extended Events don’t actually capture an event called Query Wait Statistics. Instead, you can capture one of two events, wait_info or wait_completed. Personally, in most circumstances, I’m going to capture wait_completed. It will show the waits that have finished and their duration. You may want to track wait_info in some cases where you want to see the wait start and end, the order on the waits starting, ending, etc. Most of the time, you just won’t need those details, so wait_completed will get the job done.

Secondarily, you enable Causality Tracking in the Session that you create. This marks each set of events that are caused by one another while providing them with a sequence. This allows you to group them and order the events so you can tell which wait occurred when. Combine all this with other events such as module_end, rpc_starting & rpc_completed or sql_batch_completed, and you can see when a query or procedure starts, when it ends, and the wait statistics associated with it.

Finally, but probably most importantly, you need to ensure that you filter this information. You don’t want to capture all wait statistics on your server through extended events. That is going to create a management nightmare and will probably negatively impact your system’s performance. Because the wait events capture only a few pieces of information, you will want to add Global Fields to have something to filter on to reduce the overhead and amount of data collected.

Here’s an example script that filters by database:

CREATE EVENT SESSION ProcedureWaits
ON SERVER
    ADD EVENT sqlos.wait_completed
    (SET collect_wait_resource = (1)
     WHERE (sqlserver.equal_i_sql_unicode_string(sqlserver.database_name, N'AdventureWorks2017'))),
    ADD EVENT sqlserver.module_end
    (WHERE (   sqlserver.database_name = N'AdventureWorks2017'
               AND object_name = N'ProductTransactionHistoryByReference')),
    ADD EVENT sqlserver.rpc_completed
    (WHERE (sqlserver.database_name = N'AdventureWorks2017')),
    ADD EVENT sqlserver.rpc_starting
    (WHERE (sqlserver.database_name = N'AdventureWorks2017'))
WITH (TRACK_CAUSALITY = ON);

Conclusion

Like so much else with Extended Events, there’s more here. However, if you’re interested in getting started tracking specific wait statistics, this will get you going.

---

If you want to learn some more about Extended Events and the other tools I’ve mentioned here, on March 23rd, 2018, I’ll be doing an all day session near Richmond, VA. Seating is limited, so please sign up early.

Or, on May 18th in NYC, I’ll be doing an all day seminar on query tuning tools. Seating is also limited there, so please, sign up sooner rather than later.