BT

InfoQ Homepage News SQL Server 2016: Live Query Statistics

SQL Server 2016: Live Query Statistics

Bookmarks

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
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.

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

Community comments

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

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

BT

Is your profile up-to-date? Please take a moment to review and update.

Note: If updating/changing your email, a validation request will be sent

Company name:
Company role:
Company size:
Country/Zone:
State/Province/Region:
You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.