By
Published
Mar 23 2019 03:07 PM
231
Views
First published on MSDN on Nov 14, 2016
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
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