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.
- On the tools menu click the Live Query statistics
2. Right click on a selected query in Management Studio and then click Include Live Query Statistics.
3. Select ‘Query’ from Menu Bar and then click Include Live Query Statistic
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.
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
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