Select Page

Performance troubleshooting with SQL Server 2016 Live Query Statistics

Author: Eric Russo | | November 25, 2015

Exploration is something we all do when a new releases come of a software. Along with many new features in SQL Server 2016, one of the developer and DBA friendly features is Live Query Statistics. If you are a DBA or developer, there must have been situations where a query takes a long time to execute and “actual” query plan can only be seen once execution is completed. There are multiple ways to enable the feature.

SQL Server Management Studio

As shown in screenshot below, highlighted is a small button which enables this feature of Live Query Statistics. In real life of troubleshooting slow query performance, this would be a very powerful feature. We can get the query plan when the query is actually executing. We can get insights into the execution plan and which part of the plan is getting executed at the given point of time.

It can also be enabled by right clicking on the query window and choose same option.

Also you can do this from the menu bar by clicking on “Query.”

Once it’s enabled and the query is executed, we would see a new tab when query is running called “Live Query Statistics” shown in the image below.

Here is the sample query which we have executed:

SELECT *
FROM Sales.SalesOrderDetail d
	,Sales.SalesOrderHeader h
WHERE h.SalesOrderID = d.SalesOrderID

This would need AdventureWorks sample database, which can be downloaded from http://msftdbprodsamples.codeplex.com/ .

As we can see, it provides “live” execution plan and displays various things:

  1. Overall query progress/status.
  2. Run-time execution statistics such as elapsed time.
  3. Progress of operator (% completion and time taken.)

Number of rows processed.

Activity Monitor

If we right click on server in SSMS, we have an activity monitor. If you notice in the screen below, a new grid is seen as compared to older versions of SSMS – “Active Expensive Queries.” We can see the live plan if we right-click a query and click on the “Show Live Execution Plan” option.

This option is a little tricky. To get the live plan from activity monitor, the query has to be run with “SET STATISTICS XML ON” or “SET STATISTICS PROFILE ON.” Now, you might wonder how we can see the queries live plan when its coming from application? For that query_post_execution_showplan extended event has to be enabled. Once enabled, we can see the plan for all queries which are executing on server. It must be noted that this is a developer kind of tool and should be used with care on the production server.

How it works? If you are wondering what is happening behind the scenes, it is actually a visual representation of data available in DMV sys.dm_exec_query_profiles. This is documented in books online.

Is it only for SQL Server 2016? Well, the good news is that this feature would work if we connect to SQL Server 2014 instance having at least Service Pack 1 because the DMV which is used by this feature is available in SQL Server 2014 also. But if we connect to any lower version, it would be disabled.

In summary, the Live Query Statistics feature allows us to take a look at how the query is getting executed and to find out where the bottlenecks operator. It gives us animated way to detect expensive operations.

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