SQL Server Management Studio 2016 provides the ability to view the live execution plan of an active query. This live query plan is a real-time insight into the query execution process. And, since the data is available in real-time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues.
Positive sides of Live Query Stats
Firstly, good stuff. During the post writing time I found three different aspects when the feature can bring some benefits.
# 1: Overall execution progress of the command
The very first good thing is the ability to see the query progress, even without digging into the execution plan.
Worth to mention that in previous releases, similar functionality is available for limited set of commands, like BACKUP/RESTORE, ALTER INDEX, DBCC SHRINKFILE via querying column percent_complete of sys.dm_exec_requests.
# 2: The flow order as real-time insight into the query plan
Second good thing is the ability to see query plan as dynamic execution flow. It looks pretty similar to SSIS data flow and can be used for better understanding and further debugging of the SQL Server behavior in some situations. As an example, in a previous post I recently discovered that data of temporal tables – the current and history ones – retrieved in sequential way. The following screenshot, however is not associated to that issue anyhow and simply shows the feature in action:
# 3: Actual Number of Rows
And the last one – the possibility to see actual number of rows inserted/updated/deleted/retrieved. This feature can be very valuable in scenarios where long running DML operation take place and expected number of affected rows is known.
Despite that current post suppose to be a positive in general, there are still exists few points to highlight about the subject, which are part of “not that good things” section.
#1: Performance impact
According to documentation, the feature is not lightweight:
This feature is primarily intended for troubleshooting purposes. Using this feature can moderately slow the overall query performance.
#2: The usage limitations
Operations over non-rowstore objects are not supported yet:
- Queries using columnstore indexes are not supported.
- Queries with memory optimized tables are not supported.
- Natively compiled stored procedures are not supported.
Also, there is no way to turn on live query in middle of execution.
#3: Accuracy of the numbers in statistics
The accuracy of live query statistics depends heavily on query plan estimates, therefore it means that accuracy indirectly depends on regular statistics.
How to start using the feature
- Firstly, download and install SSMS 2016 CTP 2.1. The installation will not replace existing version of management studio. However, products, like Red Gate SQL Prompt still doesn’t work in this version of the software.
- Connect to SQL Server instance. Some unknown blog says that this feature supported in SQL Server 2014 SP1 as well, however, not in SQL Server 2012.
- On the tools menu click the Live Query Statistics icon:
- Run the command. New, third tab, named as “Live Query Statistics” will appear in a results area.
I think, this is very handy and useful extension of the Data engine and SSMS functionality. It unlocks new ways of understanding of “what is going on under the hood” for nearly any SQL Server command. It can point me to the slowest join, to very expensive sort operator, and finally, I do not have to run a SELECT COUNT(*) in a parallel session anymore just to watch the progress of some long running DML command.