How many times have you come across a scenario when you execute a TSQL Query and it just runs and runs, without displaying any results and you wonder what’s going on – and finally end up cancelling the executing query?

In this article, we will look at a new feature in SQL Server 2016 called as ‘Live Query Statistics’ and how it can help us to identify potential performance bottlenecks in our TSQL queries.

Problem Statement –

When we write a complex TSQL Query by joining multiple tables, there is a chance that the query runs for a long time. This might happen for a number of reasons – large volume of data, missing indexes, issue with our TSQL query itself. This is frightening at times, especially if we are running the query against a Production environment and don’t want to create any database locking issues. The only option left with us in such a scenario is to cancel the query.

How to troubleshoot a long running query?

A long running query is one which runs for several minutes without displaying any results. Generally for troubleshooting a query performance, we look at the execution plan generated by the TSQL and then identify the expensive operators.
However for a long running query, where we have to stop the TSQL execution, there is no execution plan generated. This makes troubleshooting the query performance challenging.

What is Live Query Statistics?

Live Query Statistics is one of my favorite features in SQL Server 2016 because it makes the life of a TSQL Developer so much easier. It provides real time insight into the execution plan, while the query is still running. It is a great way to troubleshoot your long running queries, inside your SQL Management Studio, without the need of any external tools.
It gives us an ability to display the query as it executes and analyze the performance issues before the query has even finished.

I have installed SQL Server 2016 CTP 3.3 for the purpose of this blog.

What are the benefits of using Live Query Statistics?

• Overall Query Progress – 

When you execute a query, you can visually see how much percentage of the query has completed processing. For long running queries, it will help user to get an estimate on how much time the query will take to complete execution.

• Real Time insights into the SQL Query Execution process – 

When a query is submitted to SQL Server, the Query Optimizer figures out the best way to implement the request. It decides what types of joins to use, which data can be accessed through indexes, if there will be any lookup operation required and more.

Live Query Stats provides a visual representation of a progressive execution plan. It will help you to understand how SQL Server internally executes a query. You will be able to collect actual metrics about the query while it is running.

• Operator Progress – 

The Live Execution Plan shows which operator is taking more time. It exactly points out the slowest/expensive operators – which will help you to identify where the performance bottleneck is.

It is really helpful when you have bigger execution plans and can visually see which operators are taking more time.

Dotted lines indicate that the query is still running. The dotted lines change to solid lines, as soon as the query execution completes.

How to enable Live Query Statistics?

1. Using SQL Server Management Studio

Notice that there is a new icon in the toolbar. To view the Live Execution Plan, you will need to select the ‘Include Live Query Statistics’ option before executing the query.

       2. Using Activity Monitor

You can view the Live Execution Plan from the Activity Monitor, by doing a right click on the ‘Active Expensive Queries’ section and choosing the option ‘Show Live Execution Plan’

How is the live execution plan generated behind the scenes?

The Live Query Stats is actually a visual representation of data available in the data management view named sys.dm_exec_query_profiles.

SELECT * FROM sys.dm_exec_query_profiles

This DMV is new in SQL Server 2014 and can provide the same results in text-form as the graphic live animations. You can very well use this DMV to determine which part of the query is running slow.

To summarize, there are 3 reasons why I love Live Query Statistics –

• I can view the live execution plan for an active query.
• I need not wait for the query to get completed for getting the stats.
• I can easily identify the performance bottlenecks for a long running query.

Also read –

• Query Store in SQL Server 2016

Categories: Performance, SQL Server