Select Page

Performance Improvements with Live Query with SQL Server 2016

Author: Eric Russo | | April 26, 2016

New capabilities get added with every single version that get introduced in every software. SQL Server 2016 is no brainer in this challenge. There are tons of great capabilities that get added in the area of performance tuning. Let me share a story about the same. Whenever there is a query which is executing for long time in SQL Server, one of the common question would be “where exactly the query is stuck”. In such situations, DBA used to check if IO or CPU is changing by querying various DMV. If they are, then all they can say is “things are moving”. With an enterprise class of software, this kind of question should be answered without any problem. Microsoft has heard this feedback and added an enhancement in SQL Server 2016 called as “Live Query Statistics”

As the name suggests, this feature would allow DBA/developers to have a look at current state of the query execution. If you have seen query execution plan in SQL Server Management Studio (SSMS) then you can easily imagine this feature as “live” query plan where progress / run-time statistics is shown for each operator in the query plan. This includes percentage complete, number of rows processed, elapsed time etc. This feature would help in doing performance tuning.

Here is a quick animation of what you would see:

As we can see above, we can view a live execution of the query plan. The operator values are showing current progress and continuously changing values until the execution of the query.

Enabling this is very simple. In SSMS, we can do the following

1. Click on the icon for “Live Query Statistics”

2. Right click on the query area and choose “Include Live Query Statistics”

3. Use SSMS Menu, choose Query and select “Include Live Query Statistics”

For demonstration purpose, we have downloaded “AdventureWorks”, a Sample database from Microsoft download site

Here is the query which takes around four seconds but gives you a good sense of how the feature works.

use AdventureWorks2016CTP3
FROM Sales.SalesOrderDetail d
    ,Sales.SalesOrderHeader h
WHERE h.SalesOrderID = d.SalesOrderID

You will notice that while the query was running, SQL Server Management Studio immediately opened a new tab in the Results window called “Live Query Statistics”.

If you want to look at longer query execution, then you can use this:

SELECT ProductKey
FROM   FactResellerSalesXL_PageCompressed f
	,DimEmployee de
	,DimDate dd
WHERE f.EmployeeKey = de.EmployeeKey
AND f.DueDateKey = dd.DateKey

If you want to know how it’s working, then you can read about documentation of DMV dm_exec_query_profiles. We have captured the profiler and found below getting executed to get runtime information of a query.

FROM sys.dm_exec_query_profiles
WHERE plan_handle = 0x06000800AC4A452FA0D8B99D79000000010000000000000000000000000000000000000000000000000000
AND session_id = 63

Plan_handle can get fetched from sys.dm_exec_requests for corresponding session_id.

To summarize, the Live Query Statistics feature is a very useful addition to a developer’s toolbox. A live execution plan allows us to have a direct view of how the query is executing and to find out where the bottlenecks are. In the current release of SQL, there are some limitations, but hopefully they will be removed in the future. In the meantime, a DBA can now answer the question “where exactly is the query stuck?” with Microsoft’s new feature.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.