SQL Server 2016 SP1 Query Specific Wait Statistics Available Through Execution Plans

SQL Server 2016 SP1 added loads of cool new features and options. One of those is a change that expands the amount of information that is stored inside an execution plan. Since I love using Wait Statistics as a way to identify performance bottlenecks, I was literally jumping through the room when I found out execution plans now also return information about the waits the plan ran into during execution!

You can view the Wait Statistics information stored inside the execution plan of a query by first enabling the “Include Actual Execution Plan” option inside SQL Server Management Studio and then executing the query. After query execution switch to the execution plan, right-click the first operator on the left and select “Properties”. The “WaitStats” section is at the bottom of the properties:

20112016_2016sp1_ws_qp_01

As you can see, the query I executed ran into two Wait Types: ASYNC_NETWORK_IO and PAGEIOLATCH_SH. The properties also return how many times my query had to wait for each specific Wait Type and the total time it spend waiting on each Wait Type.

You can also view this information through the execution plan XML by looking at the <WaitStats> section:

20112016_2016sp1_ws_qp_02

One thing to keep in mind though, the Wait Statistics are only available inside the Actual Execution Plan, a.k.a. the execution plan that was used to execute the query. Which is pretty logical since SQL Server cannot predict what waits it will run into before running the query.

5 comments to “SQL Server 2016 SP1 Query Specific Wait Statistics Available Through Execution Plans”

You can leave a reply or Trackback this post.
    • Hi Paul,

      Thats interesting. Can you see the “WaitStats” category in the graphical execution plan?
      And are you sure you are looking at the actual instead of the estimated Execution Plan?

Write a Reply or Comment

Your email address will not be published.