Live Query Statistics in SQL Server 2016

  • Article

Hi everyone!

For this blog post, I thought I would bring attention to “Live Query Statistics” (LQS), one of the many new features and improvements of SQL Server 2016 available from SQL Server Management Studio (SSMS). LQS is an important new feature because it directly addresses a requirement often heard from SQL Server DBAs: “we need to monitor live execution of a specific query to understand where and how time and resources are being consumed”.

LQS allows the real-time monitoring of various execution metrics including CPU/memory usage, execution time, query progress and others to facilitate rapid identification of potential bottlenecks and help troubleshooting query performance issues. It also allows drill-down to live operator level statistics:

  • Number of generated rows
  • Elapsed time
  • Operator progress
  • Live warnings

Live Query Statistics - Query Plan

This remarkable feature is available in various ways in SSMS, including from the Tools menu by clicking on Live Query Statistics. Alternately, it can also be enabled by clicking on the “Include Live Query Statistics” icon as highlighted in red in the figure below before launching the execution of a query:

[caption id="attachment_3305" align="aligncenter" width="826"]Include Live Query Statistics Include Live Query Statistics[/caption]

Finally, it can also be launched to monitor queries executed by others on SQL Server 2016 by navigating in SSMS to the Activity Monitor (Right Click on the Server node in the Object Explorer and selecting Activity Monitor) and clicking on the Active Expensive Queries to list all currently executing queries. Then a simple right click on one of these currently executing queries will allow the selection of Show Live Execution Plan.

[caption id="attachment_3315" align="aligncenter" width="389"]Object Explorer -> Activity Monitor Object Explorer -> Activity Monitor[/caption]

[caption id="attachment_3325" align="aligncenter" width="952"]Activity Monitor - Active Expensive Queries Activity Monitor - Active Expensive Queries[/caption]

[caption id="attachment_3285" align="aligncenter" width="909"]Live Query Plan Live Query Plan[/caption]

Should you wish to experiment with this impressive feature, feel free to use the following query against the AdventureWorks sample database (You may need to relaunch its execution a second time if its first execution completed too quickly to allow you to see anything in LQS):

 -- Live Query Stats
-- Turn on LQS in SSMS before running this query
USE AdventureWorks2016CTP3
GO

DBCC FREEPROCCACHE
DBCC TRACEON (9481)

SELECT e.[BusinessEntityID],
       p.[Title],
       p.[FirstName],
       p.[MiddleName],
       p.[LastName],
       p.[Suffix],
       e.[JobTitle],
       pp.[PhoneNumber],
       pnt.[Name] AS [PhoneNumberType],
       ea.[EmailAddress],
       p.[EmailPromotion],
       a.[AddressLine1],
       a.[AddressLine2],
       a.[City],
       sp.[Name] AS [StateProvinceName],
       a.[PostalCode],
       cr.[Name] AS [CountryRegionName],
       p.[AdditionalContactInfo]
FROM   [HumanResources].[Employee] AS e
INNER JOIN [Person].[Person] AS p
       ON RTRIM(LTRIM(p.[BusinessEntityID])) = RTRIM(LTRIM(e.[BusinessEntityID]))
INNER JOIN [Person].[BusinessEntityAddress] AS bea
       ON RTRIM(LTRIM(bea.[BusinessEntityID])) = RTRIM(LTRIM(e.[BusinessEntityID]))
INNER JOIN [Person].[Address] AS a
       ON RTRIM(LTRIM(a.[AddressID])) = RTRIM(LTRIM(bea.[AddressID]))
INNER JOIN [Person].[StateProvince] AS sp
       ON RTRIM(LTRIM(sp.[StateProvinceID])) = RTRIM(LTRIM(a.[StateProvinceID]))
INNER JOIN [Person].[CountryRegion] AS cr
       ON RTRIM(LTRIM(cr.[CountryRegionCode])) = RTRIM(LTRIM(sp.[CountryRegionCode]))
LEFT OUTER JOIN [Person].[PersonPhone] AS pp
       ON RTRIM(LTRIM(pp.BusinessEntityID)) = RTRIM(LTRIM(p.[BusinessEntityID]))
LEFT OUTER JOIN [Person].[PhoneNumberType] AS pnt
       ON RTRIM(LTRIM(pp.[PhoneNumberTypeID])) = RTRIM(LTRIM(pnt.[PhoneNumberTypeID]))
LEFT OUTER JOIN [Person].[EmailAddress] AS ea
       ON RTRIM(LTRIM(p.[BusinessEntityID])) = RTRIM(LTRIM(ea.[BusinessEntityID]))
GO

There you have it! Another great tool from Microsoft that enables SQL Server 2016 DBAs to finally be able to monitor live resource consumption and achieve more!

Documentation on Live Query Statistics (LQS): https://msdn.microsoft.com/en-us/library/dn831878.aspx

AdventureWorks sample database download link: https://www.microsoft.com/en-us/download/details.aspx?id=49502)