Performance troubleshooting with SQL Server 2016 Live Query Statistics

By | In SQL Server | November 25th, 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:

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.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

1 thought on “Performance troubleshooting with SQL Server 2016 Live Query Statistics”
  1. Live Query Statistics do not seem to work with the newer of the monthly releases of SSMS (Build 15700 and 15800).
    It’s been working with Build 15000 and 15600, but now it shows 100% at every step right from the start although the query ist still running. Is this a known problem?