SQL 2016 Live Query Statistics

In my last post we learned how ‘Query Store’ works, now this is the time to show one other amazing feature called ‘Live Query Statistic’ that is also released with SQL 2016. Just to clarify this feature is included with SQL Management studio 2016 that means it will work if it connects to SQL version 2014.

Let’s now see how this feature can be beneficial for DBA/Consultant OR support engineers.

The steps of preparing LAB environment is same as steps mentioned in ‘Query store’ Blog.

/* Please note, the database and tables are design only for training purpose so it doesn't necessarily have the best design.*/
-- Create Database & two tables 'Region' and 'EmployeeData'
USE [master]
CREATE DATABASE QueryStoreDemo
GO

USE [QueryStoreDemo]
CREATE TABLE Region
       (
       ID INT IDENTITY(1,1),
       RNAME VARCHAR (100),
       HQ VARCHAR(100)
       )
GO

USE [QueryStoreDemo]
CREATE TABLE EmployeeCity
       (
       ID INT IDENTITY (1, 1),
       NAME VARCHAR (100),
       City VARCHAR(100), 
       RegionID INT
       )
GO

-- Insert values, the second insertion might take time (3-5 minutes) as we are inserting 6 rows 50K times. 
USE [QueryStoreDemo]
INSERT INTO Region  VALUES
       ('PUNE', 'SOUTH'),
       ('GOA', 'EAST'),
       ('NASHIK', 'WEST'),
       ('NASIK','NORTH'),
       ('SOLAPUR','Center')
GO

USE [QueryStoreDemo]
INSERT INTO EmployeeCity
VALUES
       ('Pradeep', 'Pune', 1),  
       ('Vikram', 'Mumbai', 2),
       ('Darshan', 'Goa', 3),
       ('AJAY', 'Nashik', 4),
       ('Yogesh', 'Solapur', 5)
GO 50000

With SQL 2014 OR any prior version we have two option ‘Actual execution plan’ & ‘Estimated Execution Plan’ to know query statistic. The Estimated Query Plans are created without execution and contain an approximate Execution Plan. This can be used on any T-SQL code without actually running the query.  So for example, if you had an UPDATE query you could get the Estimated Query Plan without actually running the UPDATE.

The Actual Execution Plans are created after the query complete execution and DBA need to wait till it’s complete processing of all the background steps.

Usually the Estimated and the Actual Plans have similar graphical representation, but they can differ in cases where the statistics are outdated or the query involves parallelism, etc…  Additionally you cannot create Estimated Plans for queries that create objects and work with them (i.e. a query using a temp table).

SSMS 2016 comes with Live query statistics that provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another in percentage. The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc.

Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues.

For any AD-Hoc query, Live query  statistics can be enabled by one of the available option.

  1. On the tools menu click the Live Query statistics

Enable

2. Right click on a selected query in Management Studio and then click Include Live Query Statistics.

enable2

3. Select ‘Query’ from Menu Bar and then click Include Live Query Statistic

enable3

Let’s run following query with live query statistics enabled. This may run for 1-2 minutes the execution of query will show progress of each operator.

USE [QueryStoreDemo]
SELECT 
       Region.ID,
       Region.HQ,
       Region.RNAME, 
       EmployeeCity.ID, 
       EmployeeCity.NAME,
       EmployeeCity.RegionID,
       EmployeeCity.City
FROM EmployeeCity
CROSS JOIN Region
GO

The output will be like below.

Results

If a query coming from front end (dynamic SP) OR the code logic is inside SP then one of the following line should be included in your code.

SET STATISTICS PROFILE ON
SET STATISTICS XML ON

 

Example (closing all existing query editor windows):-

-- Create Store procedure DBO.GetEMPdata
CREATE PROCEDURE DBO.GetEMPdata
AS
SET STATISTICS PROFILE ON
SELECT 
       Region.ID,
       Region.HQ,
       Region.RNAME, 
       EmployeeCity.ID, 
       EmployeeCity.NAME,
       EmployeeCity.RegionID,
       EmployeeCity.City
FROM EmployeeCity
CROSS JOIN Region
GO

 

Execute SP DBO.GetEMPdata then access live query from Activity Monitor

-- Execute SP DBO.GetEMPdata
EXEC DBO.GetEMPdata

Activity Monitor

Please note, This feature is primarily intended for troubleshooting purposes. Using this feature can moderately slow the overall query performance additionally SQL Login requires the database level SHOWPLAN permission to populate the Live Query Statistics

Advertisements

#live-query-statistics, #query-plan, #sql, #sql2016