This post is part of the serie 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the serie!
- What's new in SQL Server 2016: Json
- Comparing execution plans with SQL Server 2016
- What's new in SQL Server 2016: Dynamic Data Masking
- What's new in SQL Server 2016: Row-Level Security
- What's new in SQL Server 2016: Stretch Database
- What's new in SQL Server 2016: Live Query Statistics (this post)
- What's new in SQL Server 2016: Temporal Tables
- What's new in SQL Server 2016: Always encrypted
- SQL Server 2016 SP1 - Licensing changes
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 to see 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!