A database query times out and you don’t know why. The estimated query plan is revealing the problem, so you remove the timeout entirely. An hour later it is still running and you are no closer to getting the actual execution plan. If only there was a way to find out what is actually happening inside the server.
With Live Query Statistics in SQL Server 2016 you can now answer that question. This new feature offers the DBA a live version of the execution plan with annotations detailing which steps are currently being performed.
The display resembles what we see running a SQL Server Integration Services job inside Visual Studio, but at a much lower level of detail. Details include “the number of rows produced, elapsed time, operator progress, etc.” Here is an example from the documentation:
This feature only works against normal tables; you can’t use with when running a query that hits a memory optimized table or columnstore index. Nor can it see into natively compiled stored procedures.
This feature isn’t enabled by default, presumably because of the extra overhead that progress reporting entails. You can enable it on a session by session basis, or enable the query_post_execution_showplan extended event for server-wide access.