New Showplan enhancements

Cross post with https://aka.ms/sqlserverteam

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. Available using the latest versions of SSMS v17, when used with SQL Server 2012 SP4, SQL Server 2016 SP1 and SQL Server 2017. For SQL Server 2014 it will become available in a future Service Pack. 

 

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.

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).

Available using the latest versions of SSMS v17, when used with SQL Server 2016 SP1 and SQL Server 2017. Note that CXPACKET wait will be available in showplan with SQL Server 2017 CU3 and 2016 SP2.

One other information you can now find in showplan is trace flags. This is relevant to understand what trace flags are active during compilation, and which one (if any) actually influence compilation. Below we can see trace flags 2371, 7412 and 9481 were active during compilation (IsCompileTime = True), but 23714 and 7412 did not influence the Query Optimizer (IsCompileTime = False). You can see more information on these trace flags and others in https://aka.ms/traceflags.

These can be found in the root node of even an estimated plan, given this is a compile time ionformation. Available using the latest versions of SSMS v17, when used with SQL Server 2012 SP4, SQL Server 2016 SP1 and SQL Server 2017. For SQL Server 2014 it will become available in a future Service Pack.

Pedro Lopes (@sqlpto) – Senior Program Manager