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
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:
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.
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)