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.

image

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

image

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

image

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