I remember writing an article about Query specific wait statistics being available inside execution plans in SQL Server 2016 SP1. The way that works is when you view the actual execution plan of a query (through SSMS or the XML) you are able to see the wait information that specific query ran into during execution.

Look! Query specific wait statistics!

At that time I was very excited about this enhancement, and I still am, but back then I could only hope for some method that this information could be stored inside SQL Server.

And then….drumroll……This little line came along in the What’s New in SQL Server vNext CTP 2.0:

Could this be?….nah it couldn’t…or?….oh yes it is!

Analyzing per-query wait statistics through the Query Store

With the release of CTP 2.0 of SQL Server vNext the Query Store also captures and stores the wait types and wait times queries encountered during execution. Is it perfect? To be honest, not yet, but it is absolutely helpful if you are interested in finding out what bottlenecks your queries are encountering during execution.

Let’s dive in shall we?

What do I need to do to enable the collection of per-query wait statistics inside the Query Store?

Nothing! As soon as you enable the Query Store on a database in read-write mode the wait statistics will be captured and stored as well. One thing I would like to mention here is that the wait statistics are related to the execution plan. So while I mention per-query wait statistics and more fitting name should be per-execution plan wait statistics, but let’s be honest here, that doesn’t sound nearly as good as per-query wait statistics.

Where can I find the per-query wait statistics?

All of the wait statistics are recorded in a new Query Store DMV: sys.query_store_wait_stats.

Look at all that waiting going on there

You basically have two, meaningful, ways you can join this DMV to other Query Store DMVs. On way is through the plan_id, the other through the runtime_stats_interval_id (or both). Both can be very helpful if for instance you are interested in tracking only a specific execution plan, or want to compare wait statistics information between multiple time segments, or intervals.

But wait (no phun intended), I don’t see any wait types I know?

And you are correct sir! Apparently the Query Store groups different wait types into categories. For instance PAGEIOLATCH waits are gathered and stored in the “Buffer IO” category. Don’t be confused with the wait_stats_id. Although it seems like that number could correspond with a unique wait type it simply doesn’t at this time and it looks like it is just number.

For instance, during one execution of a query and looking at the wait statistics recorded in the actual execution plan, it ran into 6ms of PAGEIOLATCH_SH waits. Inside the sys.query_store_wait_stats that plan was recording running into 1ms of Buffer IO type waits and received a wait_stats_id of “7”.

When I executed a different query that also ran into PAGEIOLATCH_SH waits a new entry was added to the sys.query_store_wait_stats DMV for that specific plan id. Only this time the wait_stats_id was “31” as you can see in the screenshot below:

This doesn’t make sense…yet…

For me this is the one small disappointment that makes this awesome new addition a little less awesome. While it absolutely is interesting to know what category waits a query is running into, recording the actual wait types is way more helpful. Yes, we can point to the areas a bottleneck can occur using the wait categories, but we won’t be able to exactly identify the bottleneck. Keep in mind though that SQL Server vNext is still in its CTP phase. This means changes are still being made to the product before release. Let’s hope they will add a way we can see the specific wait types before release!

Ok so we got this DMV, how can we view this information inside the Query Store reports?

Hate to bring it to you, but you can’t. The built-in Query Store reports do not return any type of wait statistics sadly. Again, hopefully in a new release of SSMS. Being able to compare wait statistics on a per-query level in a graphical manner would be a massive improvement!

Conclusion

Adding per-query wait statistics and storing them inside the Query Store is a massive step towards making the Query Store the ultimate place to visit when dealing with performance. There is still room for some improvements to make it perfect and I hope new CTP releases will add more functionality!