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