Query Store Wait Statistics with sys.query_store_wait_stats

The second best thing to questions that people ask is when I sit down to write a book. It’s so easy to miss things in the day-to-day grind of doing work. Then, late at night, you’re working on a chapter, so you read up on the documentation to ensure that you’re not missing anything. Of course, then you find, yes, you are missing something. In my case, sys.query_store_wait_stats.

sys.query_store_wait_stats.

If you follow the link above, it’ll give you what you need to know, but, I figured I’d provide a little more clarity because I think there are some pitfalls in using this data.

I love Query Store (do a search to see all the exploration I’ve done with it). One of my favorite things is the time intervals. It breaks the aggregates of the runtime data, and now the wait statistics, into smaller chunks of time (1 hour by default). Why is this so wonderful? Because it gives you a “before” and “after” so that you can compare how the query is behaving now to yesterday or yesterday to last week or… check out this blog post that includes a query to get the work done.

Now, if you read the docs, you’re likely to write a query something like this:

SELECT qsq.query_id,
qsp.plan_id,
qsp.query_plan,
qsrs.avg_duration,
qsrs.stdev_duration,
qsws.wait_category_desc,
qsws.avg_query_wait_time_ms,
qsws.stdev_query_wait_time_ms
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_wait_stats AS qsws
ON qsws.plan_id = qsp.plan_id
WHERE qsq.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference');

Why do I say that? Because that’s what I did. Problem is, the data returned looks like this:

What the heck is that mess? Well, it’s a combination of several things. First, you can have more than one wait type for a given time interval. Second, in the query we’re, correctly, joining the waits and the runtime stats to the plan. However, that means that they’re not in any way related to each other so we get a sort of Cartesian product out of the whole thing. If you read the documentation, it talks about aggregating by plan_id (of course), stats_interval_id, execution_type and the wait_category. Same thing if you read the docs on sys.query_store_runtime_stats, it groups by plan_id, stats_interfal_id and execution_type.

Therefore, if you’re trying to combine a specific wait, or set of waits (there can be multiple), with a specific set of runtime metrics, you need to modify your query to this:

SELECT qsq.query_id,
qsp.plan_id,
qsp.query_plan,
qsrs.avg_duration,
qsrs.stdev_duration,
qsws.wait_category_desc,
qsws.avg_query_wait_time_ms,
qsws.stdev_query_wait_time_ms
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_wait_stats AS qsws
ON qsws.plan_id = qsrs.plan_id
AND qsws.execution_type = qsrs.execution_type
AND qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
WHERE qsq.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference');

Joining query_store_runtime_stats and query_store_wait_stats ensures that you’re comparing appropriate data. The first query returned 77 rows. This one returns just 17 because the wait statistics and the runtime statists are now synchronized:

Conclusion

The bad news is, this is SQL Server 2017 and Azure SQL Database only. If you’re on 2016, you can cross your fingers and hope for the addition in a CU or SP. However, now, we can also see wait statistics along with our runtime statistics and the query plans inside of Query Store.