Select Page

Performance troubleshooting with SQL Server 2016 Live Query Statistics

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.

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

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on 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.

CONTACT US

Work for Us

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

EXPLORE JOBS