BT

SQL Server 2016: Live Query Statistics

| by Jonathan Allen Follow 638 Followers on Jul 06, 2015. Estimated reading time: 1 minute |

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.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss
BT