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

Continue reading

Advertisements

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