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:

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:

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.