SQL 2016 Query Store

One of amazing new feature that came up with SQL 2016 release is ‘Query store’. This is going to be the game changer and most useful for SQL DBA, Support Engineers and consultant those used to rely on ‘plan cache’ (AKS ‘Procedure Cache’) to track down query statistics and execution plans for queries run in past. Plan cache is useful to know what got change after XYZ changes such as missing index, application upgrade…etc.

The biggest disadvantage with ‘planned cache’ is that it remain in memory and get flush out once SQL service restart (OR sever reboot). High memory pressure on SQL Instance also cause plan cache to flush out from memory.

Now with SQL 2016, if ‘Query store’ is enabled, execution plans are saved on disk and can be accessed even if server is rebooted. This new feature is enabled on an individual database with minimum configuration change. The saved execution plan can be accessed using GUI reports OR built in DMV queries.

Let’s now look at our LAB to know the configuration and use cases of ‘Query plan’.

Prepare your environment

/* 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

Enable Query Store

Query Store can be configured from the properties of database as shows below.

enable

You will find few additional configuration option when you enable using SQL query.

USE Master
ALTER DATABASE QUERYStoreDemo 
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = 
    (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000
);

Description about configuration options.

OPERATION_MODE – This can be READ_WRITE or READ_ONLY.
CLEANUP_POLICY – Specifies through the STALE_QUERY_THRESHOLD_DAYS the number of days for the query store to retain data.
DATA_FLUSH_INTERVAL_SECONDS – Interval in seconds in which the data is written from memory to data file allocated for query store.
MAX_STORAGE_SIZE_MB – Maximum size of the total data in the Query Store.
INTERVAL_LENGTH_MINUTES – Gives the interval at which the data from runtime execution stats is aggregated.
SIZE_BASED_CLEANUP_MODE – When the data in the Query Store gets close to the configured number in MAX_STORAGE_SIZE_MB this option can control the automatic cleanup process.
QUERY_CAPTURE_MODE – Gives the Query Store option to capture all queries or relevant queries based on execution count and resource usage.
MAX_PLANS_PER_QUERY – The maximum number of execution plans maintained for queries.


Run following two badly written select statement, first statement is cross join example to show long duration of query and second is a normal inner join but run 10 times.

USE [QueryStoreDemo]
SELECT * FROM EmployeeCity
CROSS JOIN Region
GO

USE [QueryStoreDemo]
SELECT NAME, City, Region.HQ FROM EmployeeCity
INNER JOIN Region ON  Region.ID=EmployeeCity.RegionID
GO 10

Query store can be accessed using the option available in Management studio.

Expand ‘Database’ then expand ‘Query store’. In my following example, we are running ‘Top Resource Consuming Queries’ report, that shows both above select queries those are either bad written OR running without using indexes.

QueryStore Access.jpg

Refer  to know more about remaining options. Here is the example of reviewing information using DMV.

/* Number of executions for each query. */
use QueryStoreDemo
SELECT 
       QUERY.query_id, 
       QUERYTEXT.query_text_id, 
       QUERYTEXT.query_sql_text,
       SUM(RUNTIMESTATS.count_executions) AS total_execution_count
FROM 
       sys.query_store_query_text   
AS
QUERYTEXT 
       INNER JOIN sys.query_store_query AS QUERY ON QUERYTEXT.query_text_id = QUERY.query_text_id 
       INNER JOIN sys.query_store_plan  AS QUERYPLAN ON QUERY.query_id = QUERYPLAN.query_id 
       INNER JOIN sys.query_store_runtime_stats AS RUNTIMESTATS ON QUERYPLAN.plan_id = RUNTIMESTATS.plan_id
GROUP BY QUERY.query_id, QUERYTEXT.query_text_id, QUERYTEXT.query_sql_text
ORDER BY total_execution_count DESC;

You will notice, our select statement that executed 10 times is shown below.

dmv1.jpg

/* The number of queries with the longest average execution time within last hour. */
SELECT TOP 10 
       RUNTIMESTATS.avg_duration, 
       QUERYTEXT.query_sql_text, 
       QUERY.query_id,
       QUERYPLAN.plan_id, 
       GETUTCDATE() AS CurrentUTCTime,
    RUNTIMESTATS.last_execution_time
FROM 
       sys.query_store_query_text   
AS QUERYTEXT INNER JOIN 
       sys.query_store_query            AS QUERY        ON QUERYTEXT.query_text_id = QUERY.query_text_id INNER JOIN 
       sys.query_store_plan             AS QUERYPLAN    ON QUERY.query_id = QUERYPLAN.query_id INNER JOIN 
       sys.query_store_runtime_stats AS RUNTIMESTATS ON QUERYPLAN.plan_id = RUNTIMESTATS.plan_id
WHERE RUNTIMESTATS.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY RUNTIMESTATS.avg_duration DESC;

You will notice, two of our badly written queries are listed in most expensive queries.

dmv2.jpg

You can purge ‘Query Store’ data OR disable this feature using following SQL queries.

-- Clear 'Query Store' data. 
USE [master]
ALTER DATABASE [QueryStoreDemo] SET QUERY_STORE CLEAR
GO

-- Disable 'Query Store'. 
USE [master]
ALTER DATABASE [QueryStoreDemo] SET QUERY_STORE = OFF
GO
Advertisements

#plancache, #query-performance, #querystore, #sql