Getting Wait Stats From The Query Store

Erin Stellato shows how to retrieve wait stats per query from Query Store:

In SQL Server 2016 a new DMV is exposed, sys.dm_exec_session_wait_stats, which provides information about waits for an existing, active session. If you know the session_id, you can track waits for a query when it starts and when it completes (snapshot the information at the beginning and end of the query and then diff the information). The challenge is that you have to know the session_id for the query, and you have to set up data capture in advance – which isn’t trivial when you’re in the midst of a high priority issue.

Wait statistics information exists in an actual execution plan starting in SQL Server 2016 SP1. Only the top 10 waits are captured, and there are limitations in terms of what this data represents. For example, CXPACKET is ignored and not included in the output, but it will be included in 2016 SP2 and 2017 CU3 and up – where irrelevant parallelism waits are instead captured by CXCONSUMER (which will not be included in actual plan waits).

So how can we see what a specific query is truly waiting on? We can use Query Store! SQL Server 2017 includes the capture of wait statistics information in Query Store, and this functionality is also available in Azure SQL Database. Wait statistics are tied to a query plan, and are captured over time, just like the runtime statistics. The addition of wait statistics information in Query Store was the number one feature request after its initial release, and all that information together creates powerful troubleshooting capabilities.

Read on for a demo-filled post.  It’s hard to overstate just how useful this information is for query tuning.

Related Posts

Best Practices for Query Store

Erin Stellato ties together a number of best practices around Query Store: I’m a huge fan of Query Store, which regular readers may know, but there’s a need to write a bit more about Query Store best practices.  This isn’t a “you must use this feature” post, this is a “here is what you must […]

Read More

Usability Issues with Query Store

Lonny Niederstadt has a new series on usability “soft spots” with Query Store. Part one looks at plan identifiers: Yeah. That’s a lotta plans in the right-hand legend.  22 of them.  In a not very helpful order.  In fact… though I’ve tried to figure it out, I don’t know what type of order is used […]

Read More

Categories

November 2017
MTWTFSS
« Oct Dec »
 12345
6789101112
13141516171819
20212223242526
27282930