Select Page

Performance Improvements with Live Query with SQL Server 2016

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.

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.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

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.