This post is part of the series 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the series!
The final version of SQL Server 2016 has been released recently. So, it's time to take a look at the new features provided by this version. Today we will discover Live Query Statistics.
SQL Server 2014 introduced the DMV sys.dm_exec_query_profiles (doc) to get information about the progress of a running query. Live Query Statistics adds a graphical interface to this DMV. Indeed Live Query Statistics allows seeing the execution plan of a query during its execution. This execution plan displays the progress of the query processing as well as the execution statistics of each operator such as the number of rows produced, the execution time, etc. Since this data is available in real-time, it is easier to understand why some requests are long.
To enable this feature just click on the new button "Include Live Query Statistics":
Then when executing the query, we see the overall execution plan, as well as the progress of each operation:
- Dashed lines: operations waiting for execution
- Animated dashed lines: ongoing operations
- Full lines: completed operations
Note that Live Query Statistics does not work with all queries: queries that use Columnstore indexes, Memory-Optimized Tables, or natively compiled stored procedures mode are not supported. This may need to change in the next updates. Also, this feature may slightly reduce the performance of the query.
Now you have to make complex queries to have time to enjoy the beautiful animation!
Do you have a question or a suggestion about this post? Contact me!
