SQL Server 2017: query_store_wait_stats

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.

And here are the results.

Descriptive

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.

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.

The Internet ends here at plan_persist_wait_stats

, , ,
Previous Post
SQL Server 2017: Unpublished Gems
Next Post
[Video] Office Hours 2017/04/19 (With Transcriptions)

5 Comments. Leave new

  • Enrico van de Laar
    April 22, 2017 2:07 pm

    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.

    Reply
  • your interesting “uninteresting Query Store query” returns me:

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.query_store_wait_stats’.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}