The missing link
Back when the Query Store first got announced, I was amazed.
The amount of information it gathered and persisted between reboots was such a big improvement over the plan cache, plus being able to force plans was really cool.
I was always surprised that wait stats weren’t a part of that, especially with 2016 adding session level wait stats, and run time info to Extended Events and Query Plans.
After all, wait stats really are the bread and butter of troubleshooting SQL Server bottlenecks.
SQL Server vNext
Or SQL Server 2017. Whatever. It adds a new Query Store DMV called query_store_wait_stats. So what’s in there?
Well, you know when you used to open up Activity Monitor, back before you learned how lousy Activity Monitor is?
Right now, that’s kind of what this DMV shows you.
Here’s my uninteresting Query Store query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT qsws.plan_id, qsws.wait_stats_id, qsrs.runtime_stats_interval_id, count_executions, qsws.wait_category_desc, qsws.total_query_wait_time_ms, qsws.avg_query_wait_time_ms, qsws.last_query_wait_time_ms, qsws.min_query_wait_time_ms, qsws.max_query_wait_time_ms, qsqt.query_sql_text, x.actual_xml FROM sys.query_store_wait_stats qsws JOIN sys.query_store_plan AS qsp ON qsws.plan_id = qsp.plan_id JOIN sys.query_store_runtime_stats AS qsrs ON qsrs.plan_id = qsp.plan_id JOIN sys.query_store_query AS qsq ON qsq.query_id = qsp.query_id JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id = qsqt.query_text_id CROSS APPLY ( SELECT TRY_CONVERT( XML, qsp.query_plan) AS actual_xml ) AS x WHERE qsws.plan_id = 62 AND qsrs.execution_type = 0 |
And here are the results.
There’s a wait_stats_id column, but I don’t see that it maps to anything right now. I was hoping that it mapped to Extended Events wait stats map keys, but it doesn’t appear to.
|
1 2 3 4 |
SELECT dxmv.map_key, dxmv.map_value FROM sys.dm_xe_map_values AS dxmv WHERE dxmv.name = 'wait_types' ORDER BY map_key |
So for now, you just have to take the description of the wait stats for what they are until there’s something to link the actual wait types to.
But still, this is really cool, and a great step towards making Query Store a total performance troubleshooting and profiling tool.
Thanks for reading!
Update (04/25/2017): After a conversation the Patron Saint Of Databases, it doesn’t look like the wait_stats_id column will map to more specific wait stat types, and the wait stats are going to continue to be bucketed by troubleshooting scenario. Logging specific wait types adds significant overhead to collection, and table significant bloat.
Update (05/01/2017): Documentation for this DMV fell out of the sky and landed here.
Brent says: to guess what it might map to, I checked sys.all_columns looking for names like ‘%wait%’ and like ‘%id%’, and I didn’t get a good answer – but I came up with another question. There’s something called sys.plan_persist_wait_stats, and it doesn’t appear to be documented anywhere yet. Hmm.
5 Comments. Leave new
Interesting stuff indeed!
What I’m wondering from the image: is the plan in the actual_xml column the actual plan that was used when executing the query? If so this is a major change since the Query Store only stored the estimated plan before.
I see it’s the plan from sys.query_store_plan, so still estimated….
One can hope 🙂
Hahaha, yeah. But hey, I’ll take it.
your interesting “uninteresting Query Store query” returns me:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.query_store_wait_stats’.
Sounds like you’re either not on 2017, or you’re in a database that doesn’t have Query Store turned on?