Troubleshooting long running queries in SQL Server

By Samir Behara on October 29, 2017 • ( 0 )

Problem Statement –

Diagnosing performance issues with long running queries is challenging. Generally for troubleshooting a query performance, we look at the execution plan generated by the T-SQL query and then identify the expensive operators. However for a long running query, where we have to stop the T-SQL execution prior to its completion, there is no execution plan generated.

When you submit a T-SQL query to SQL Server, it goes through multiple processing in the Relational Engine and then the Storage Engine.

In the Relational Engine, the query is parsed to ensure that the T-SQL syntax is correct and then the Query Optimizer processes the query and generates an execution plan. Once done, the execution plan is sent to the Storage Engine to fetch or update the underlying data.

The role of Query Optimizer is crucial in this entire process. It generates and evaluates multiple execution plans and ultimately chooses the plan with the lowest cost. It looks at how best to process the requested T-SQL and which indexes/joins/operators to use. Once the plan is created, it is stored in plan cache – unless there is an identical plan already existing in the cache.

Resolution –

Live Query Statistics in SQL Server 2016 comes to our rescue. 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 SQL Server Management Studio, without the need of any external tools. It displays real time query insights as it executes and helps you to analyze/identify the performance issue before the query has even finished its execution.

There is a new icon the SSMS tool bar called as ‘Include Live Query Statistics‘. Once you click the button before executing a T-SQL query, you should be able to see the live query statistics as the query starts executing.

Live Query Statistics Benefits

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 you to get an estimate on how much more time the query will take to complete execution.
Live Query Statistics 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.
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.

In the image below, the dotted lines indicate that the query is still running. The dotted lines change to solid lines, as soon as the query execution completes. A quick glance is all you need to identify the slowest operator in the Execution Plan below – look at the operator percentage completion. Live Query Statistics Demo

Behind the scenes:

The Live Query Statistics is actually a visual representation of data available in the DMV named 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.