Troubleshooting with Live Query Statistics


Among the many new features in SQL Server 2016, one of the developer- and DBA-friendly features is Live Query Statistics. If you are a database administrator or developer, you have experienced situations where a query takes a long time to execute. The “actual” query plan can only be seen once execution is completed. With Live Query Statistics, one does not have to wait to get the query plan. There are multiple ways to enable the feature.

SQL Server Management Studio

As shown in screenshot below, highlighted is a small button which enables Live Query Statistics. In real life, when troubleshooting slow query performance, this will 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 any given point of time.

Live Query Statistics can also be enabled by right clicking on the query window, then choosing the option, "Include Live Query Statistics."

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

Once Live Query Statistics is enabled and the query is executed, we would see a new tab when the 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 a “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.)
  4. Number of rows processed.

Activity Monitor

If we right-click on server in SSMS, we have an activity monitor. 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 the 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 query's 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's 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 appear. It gives us an animated way to detect expensive operations.