SQL Server 2016 - Query Store

I was taking a Plural sight introduction class. Here I am sharing what I learnt from the course.

SQL Server 2016 Query Store

1. Enable on a per database level
2. Data persisted in internal tables in the user databases
3. Restored databases will have a query store database

Enabling Query Store with GUI

Enable Query Store using Script

USE [master]

GO

ALTER DATABASE [DBA] SET QUERY_STORE = ON

GO

ALTER DATABASE [DBA] SET QUERY_STORE (OPERATION_MODE = READ_ONLY)

GO

USE [master];

GO

ALTER DATABASE [DBA] 

SET QUERY_STORE = ON;

Go

ALTER DATABASE [DBA] 

SET QUERY_STORE (

OPERATION_MODE = READ_WRITE, 

CLEANUP_POLICY = 

(STALE_QUERY_THRESHOLD_DAYS = 30), 

DATA_FLUSH_INTERVAL_SECONDS = 900,  

INTERVAL_LENGTH_MINUTES = 60, 

MAX_STORAGE_SIZE_MB = 100, 

QUERY_CAPTURE_MODE = ALL, 

SIZE_BASED_CLEANUP_MODE = AUTO, 

MAX_PLANS_PER_QUERY = 200);

GO

Query Store collection setting

• Operation Mode = Read_Write | Read Only
• Query_Caputre_Mode = ALL | AUTO | NONE
• Max Plans Per Query = 200 different plan for a given query (default)
• Max Storage Size MB = 2 GB (Start with)
• CleanUp_Policy = (Stale query threshold days) 30 days
• Sized based cleanup mode  = Auto | OFF

USE [master];

GO

ALTER DATABASE [DBA] SET QUERY_STORE (

OPERATION_MODE = READ_WRITE,

/*

READ_WRITE = data collection

(default)

READ_ONLY = no data collection 

*/

QUERY_CAPTURE_MODE = ALL,

/* 

are ALL queries captured, 

or only "relevant" ones? 

ALL = every query executed 

(default)

AUTO = infrequent & insignificant

NONE = nothing new added

*/

MAX_PLANS_PER_QUERY = 200);

/*

INT data type

*/

GO

NOTE : Changing query store setting in a database will flush all cached plan from the particular database.

Query Store GUI

Permission to use Query Store

• VIEW DATABASE STATE
• db_owner needed to force/unforced plans

What is Query Store capturing?

Runtime Stats Store(Performance data)

• Compile time
• last execution time
• Duration
• CPU
• logical reads
• Physical reads
• Write

Plan Store

• Query text
• Query plan

Script to check Query store Setting in DMV

SELECT 

[actual_state_desc], 

[readonly_reason], 

[desired_state_desc], 

[current_storage_size_mb], 

    [max_storage_size_mb], 

[flush_interval_seconds], 

[interval_length_minutes], 

    [stale_query_threshold_days], 

[size_based_cleanup_mode_desc], 

    [query_capture_mode_desc], 

[max_plans_per_query]

FROM [sys].[database_query_store_options];

GO

Remove everything from Query Store

--Truncating sys tables behind the scenes

ALTER DATABASE DBA  SET QUERY_STORE CLEAR;GO

Query store System Views

• sys.query_store_query
• sys.query_store_query_text
• sys.query_store_plan
• sys.query_store_runtime_stats

Query Store system views script

Query compile and optimization information

SELECT 
[qst].[query_text_id], 
[qsq].[query_id], 
[qsq].[object_id], 
[qsq].[context_settings_id], 
[qst].[query_sql_text], 
[qsq].[initial_compile_start_time], 
[qsq].[last_compile_start_time],
[qsq].[last_execution_time], 
[qsq].[avg_compile_duration], 
[qsq].[count_compiles], 
[qsq].[avg_optimize_cpu_time], 
[qsq].[avg_optimize_duration]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id];
GO

Query plan and execution information

SELECT
[qsq].[query_id], 
[qsp].[plan_id],
[qsq].[object_id],
[qsq].[initial_compile_start_time],
[qsq].[last_compile_start_time], 
[rs].[first_execution_time],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
TRY_CONVERT(XML, [qsp].[query_plan]),
[qsp].[query_plan],
[rs].[count_executions],
[qst].[query_sql_text]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] 
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qsp].[query_id] = 3

Queries executed in the last 8 hours with multiple plans

SELECT
[qsq].[query_id], 
COUNT([qsp].[plan_id]) AS [PlanCount],
[qsq].[object_id], 
MAX(DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())), 
[qsp].[last_execution_time])) AS [LocalLastExecutionTime],
MAX([qst].query_sql_text) AS [Query_Text]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
WHERE [qsp].[last_execution_time] > DATEADD(HOUR, -8, GETUTCDATE())
GROUP BY [qsq].[query_id], [qsq].[object_id]
HAVING COUNT([qsp].[plan_id]) > 1;
GO

Confirm the plan that's being used now

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
SELECT 
[qs].[execution_count], 
[qs].[last_execution_time],
[s].[text], 
[qp].[query_plan], 
[qs].[plan_handle]
FROM [sys].[dm_exec_query_stats] AS [qs]
CROSS APPLY [sys].[dm_exec_query_plan] ([qs].[plan_handle]) AS [qp]
CROSS APPLY [sys].[dm_exec_sql_text]([qs].[plan_handle]) AS [s]
JOIN [sys].[dm_exec_cached_plans] AS [cp] 
ON [qs].[plan_handle] = [cp].[plan_handle]
WHERE [s].[text] LIKE '%CustomerTransactionInfo%';
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO

Query Store for a stored proc

SELECT

[qsq].[query_id], 

[qsp].[plan_id], 

[qsq].[object_id], 

[rs].[count_executions],

DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())), 

[qsp].[last_execution_time]) AS [LocalLastExecutionTime],

[qst].[query_sql_text], 

ConvertedPlan = TRY_CONVERT(XML, [qsp].[query_plan])

FROM [sys].[query_store_query] [qsq] 

JOIN [sys].[query_store_query_text] [qst]

ON [qsq].[query_text_id] = [qst].[query_text_id]

JOIN [sys].[query_store_plan] [qsp] 

ON [qsq].[query_id] = [qsp].[query_id]

JOIN [sys].[query_store_runtime_stats] [rs] 

ON [qsp].[plan_id] = [rs].[plan_id]

WHERE [qsq].[object_id] = OBJECT_ID(N'usp_GetMovie');

GO