New Showplan enhancements

08

Nov

New Showplan enhancements

New Showplan enhancements

Source: https://blogs.msdn.microsoft.com/sql_server_team/new-showplan-enhancements/

 

If you follow this blog, you have seen that in the past few releases we have continuously included a number of diagnostic improvements to Showplan. You can read about some of them here.

Looking at the actual execution plan is one of the most used performance troubleshooting techniques. Having information on elapsed CPU time and overall execution time, together with session wait information in an actual execution plan allows a DBA to use showplan to troubleshoot issues away from the server, and be able to correlate and compare different types of waits that result from query or schema changes.

A few months ago we had introduced exposed in SSMS some of the per-operator statistics, such as CPU and elapsed time per thread. More recently, we have introduced overall query CPU and elapsed time tracking for statistics showplan xml (both in ms). These can be found in the root node of an actual plan. 

image image

And also included the top 10 waits that the execution was waiting on (includes WaitType, WaitTimeMs and WaitCount), based on sys.dm_exec_session_wait_stats. Most common sleep and idle waits are filtered out from the actual plan, so that it becomes easier to really see the relevance of non-idle waits for query performance.

image

This allows a user to correlate waits with overall times, and be more precise in what to look for to improve query performance. For example, in the picture below, I can correlate the overall elapsed time with the top waits, see that CXPACKET were the most prevalent, that this query is running with DOP 12, and choose to reduce DOP as a way to address this (among other actions possible).

image

Note that CXPACKET wait will be available in showplan with SQL Server 2017 CU3 and 2016 SP2.

Pedro Lopes (@sqlpto) – Senior Program Manager