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 https://www.microsoft.com/en-us/download/details.aspx?id=49502

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

use AdventureWorks2016CTP3
SELECT *
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
	,de.FirstName
	,dd.CalendarYear
	,dd.CalendarQuarter
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.

SELECT *
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 Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

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.

CONTACT US

Work for Us

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

EXPLORE JOBS