New Showplan XML properties in SSMS October Release

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate node and operator.

This caused some friction, and so we are happy to announce that in the latest (October) release of SSMS, these attributes are now readily available in the Properties window of an Actual Execution Plan, in the scope of each operator.

Here’s an example with the same query as the March post, where we can see the Actual Number of Rows returned by each of the two threads executing the index seek.

And also I/O statistics per thread. This shows that this seek had fairly the same distribution of I/O per thread.

Last but not least, the CPU and Elapsed time per thread.

NOTE: ActualCPUms and ActualElapsedms will show cumulative values for the node and its children when executing in Row Mode, and will show just the single node values if executing in Batch Mode.

All this was added to Showplan XML with the intent of having more relevant context information on the execution in one single place. This helps query troubleshooting by minimizing different sources on where to obtain relevant data, and allowing context information to be available inline when analyzing an execution plan (for example, you can skip capturing STATISTICS IO separately).

Pedro Lopes (@sqlpto) – Senior Program Manager

Comments

• Anonymous
  November 15, 2016
  Thank you to the team - that is really a super great benefit!
• Anonymous
  November 17, 2016
  Bring on the queriesssss!!! :)Excellent work!!! Really good!!!
• Anonymous
  November 08, 2017
  When will the wait states be exposed in a relational table?
  • Anonymous
    November 08, 2017
    Hello Wes, can you please elaborate? What's your use case?