SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

sys.query_store_wait_stats results mixed with runtime stats

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:

sys.query_store_wait_stats correctly synched with sys.query_store_runtime_stats

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.

 

The post Query Store Wait Statistics with sys.query_store_wait_stats appeared first on Grant Fritchey.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

Comments

Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...