/*****************************************************************************************
Demo 1 - Maintaining Query Store
Antonios Chatzipavlis
SQLschool.gr
*****************************************************************************************/
-----------------------------------------------------------------------------------------
-- Find database with Query Store Enabled
-----------------------------------------------------------------------------------------
use master;
go
select name as database_data_with_QS
from sys.databases
where is_query_store_on = 1;
go
-----------------------------------------------------------------------------------------
-- Get Query Store configuration options
-----------------------------------------------------------------------------------------
use TSQLV5;
go
select *
from sys.database_query_store_options;
go
use master;
go
declare @qs nvarchar(max) = N'select {db1} as database_name,* from {db2}.sys.database_query_store_options union ';
declare @stm nvarchar(max) ='';
select @stm += REPLACE(REPLACE(@qs,'{db1}',QUOTENAME(name,'''')),'{db2}',QUOTENAME(name))
from sys.databases
where is_query_store_on = 1
order by name;
set @stm = left (@stm,len(@stm)-6);
print @stm
exec (@stm);
go
-----------------------------------------------------------------------------------------
-- Monitoring Desired vs. Actual State and Read only reason
-----------------------------------------------------------------------------------------
use master;
go
declare @qs nvarchar(max) =
N'select {db1} as database_name,actual_state_desc,desired_state_desc,
case readonly_reason
when 0 then ''''
when 1 then ''Database is in read-only mode''
when 2 then ''Database is in single-user mode''
when 4 then ''Database is in emergency mode''
when 8 then ''Database is a secondary replica (Always On and Azure SQL Database geo-replication)''
when 65536 then ''Reached limit set by MAX_STORAGE_SIZE_MB''
when 131072 then ''The number of different statements has reached memory limit. In this case you should consider remove queries from the Query Store or upgrading service tier. Applies to Azure SQL Database''
when 262144 then ''In-memory size limit has been hit. Items will be persisted to disk until space is freed up in memory. Query Store will be temporarily in read-only mode. Applies to Azure SQL Database''
when 524288 then ''Database has reached disk size limit so Query Store can no longer grow. Applies to Azure SQL Database''
else ''Unknown''
end as readonly_reason_desc
from {db2}.sys.database_query_store_options union ';
declare @stm nvarchar(max) ='';
select @stm += REPLACE(REPLACE(@qs,'{db1}',QUOTENAME(name,'''')),'{db2}',QUOTENAME(name))
from sys.databases
where is_query_store_on = 1
order by name;
set @stm = left (@stm,len(@stm)-6);
print @stm
exec (@stm);
go
-----------------------------------------------------------------------------------------
-- Monitoring Space Usage
-----------------------------------------------------------------------------------------
/*
If you do set the CLEANUP_POLICY to AUTO, you will need to monitor
the space usage yourself to make sure Query Store doesn’t switch to read-only mode.
By default, Query Store will, at 90% capacity, clean up to an 80% capacity.
If you have the MAX_STORAGE_SIZE_MB size set too low and have a large amount
of transactions coming through, it is possible for Query Store to grow bigger
than the max size specified.
You will want to put this code in a SQL Agent Job and add code to email you to alert
when Query Store is near capacity, so that you may address it.
*/
use master;
go
declare @qs nvarchar(max) =
N'select {db1} as database_name,current_storage_size_mb, max_storage_size_mb
from {db2}.sys.database_query_store_options
where ((current_storage_size_mb * 1.00) / (max_storage_size_mb * 1.00)) >= 90.00
and size_based_cleanup_mode_desc = ''OFF''
union ';
declare @stm nvarchar(max) ='';
select @stm += REPLACE(REPLACE(@qs,'{db1}',QUOTENAME(name,'''')),'{db2}',QUOTENAME(name))
from sys.databases
where is_query_store_on = 1
order by name;
set @stm = left (@stm,len(@stm)-6);
print @stm
exec (@stm);
go
-----------------------------------------------------------------------------------------
-- Clear Query Store
-----------------------------------------------------------------------------------------
alter database tsqlxxl set query_store clear all;
go
use tsqlxxl;
go
exec sys.sp_query_store_flush_db;
go
-----------------------------------------------------------------------------------------
-- Removing Plans and Queries
-----------------------------------------------------------------------------------------
use AdventureWorks;
go
select *
from sys.query_store_plan;
go
use AdventureWorks;
go
execute sys.sp_query_store_remove_plan @plan_id = <plan_id>;
go
use AdventureWorks;
go
execute sys.sp_query_store_remove_query @query_id = <query_id>;
-----------------------------------------------------------------------------------------
-- Reset Runtime Statistics for a Plan
-----------------------------------------------------------------------------------------
use AdventureWorks;
go
select *
from sys.query_store_runtime_stats;
go
use AdventureWorks;
go
execute sys.sp_query_store_reset_exec_stats @plan_id = <plan_id>;
-----------------------------------------------------------------------------------------
-- Fixing Query Store when it is in Error State
-----------------------------------------------------------------------------------------
use tsqlv5;
go
--try changing to read_write
if exists (select * from sys.database_query_store_options where actual_state=3)
begin
alter database tsqlv5 set query_store = off;
alter database tsqlv5 set query_store (operation_mode = read_write);
end
-- run sys.sp_query_store_consistency_check;
if exists (select * from sys.database_query_store_options where actual_state=3)
begin
exec sys.sp_query_store_consistency_check;
alter database tsqlv5 set query_store = off;
alter database tsqlv5 set query_store (operation_mode = read_write);
end
--run purge query store
if exists (select * from sys.database_query_store_options where actual_state=3)
begin
alter database tsqlv5 set query_store clear;
alter database tsqlv5 set query_store (operation_mode = read_write);
end
/*****************************************************************************************
Demo 2 - Catalog Views
Antonios Chatzipavlis
SQLschool.gr
*****************************************************************************************/
use AdventureWorks;
go
select *
from sys.database_query_store_options;
select *
from sys.query_store_runtime_stats;
select *
from sys.query_store_runtime_stats_interval;
select *
from sys.query_store_plan;
select *
from sys.query_store_query_text;
-----------------------------------------------------------------------------------------
-- Context Settings
-----------------------------------------------------------------------------------------
select *
from sys.query_context_settings;
select
q.query_id,
qt.query_sql_text,
qs.plan_handle,
q.context_settings_id
from sys.query_store_query q
inner join sys.dm_exec_query_stats qs
on q.last_compile_batch_sql_handle =
qs.sql_handle
inner join sys.query_store_query_text qt
on q.query_text_id = qt.query_text_id
inner join sys.query_context_settings cs
on cs.context_settings_id = q.context_settings_id
--where qt.query_sql_text like '%<value>%'
order by q.query_id
select *
from sys.dm_exec_plan_attributes(0x0600160084B38F19402E2941A502000001000000000000000000000000000000000000000000000000000000)
where attribute = 'set_options';
create or alter function fn_querystoresetoptions (@setoptions as int)
returns varchar(max)
as
begin
declare @result varchar(max)='',
@setoptionfound int
declare @setoptionslist table
(
[value] int,
[option] varchar(60)
)
insert into @setoptionslist
values
(1,'ANSI_PADDING'),
(2,'Parallel Plan'),
(4, 'FORCEPLAN'),
(8, 'CONCAT_NULL_YIELDS_NULL'),
(16, 'ANSI_WARNINGS'),
(32, 'ANSI_NULLS'),
(64, 'QUOTED_IDENTIFIER'),
(128, 'ANSI_NULL_DFLT_ON'),
(256, 'ANSI_NULL_DFLT_OFF'),
(512, 'NoBrowseTable'),
(1024, 'TriggerOneRow'),
(2048, 'ResyncQuery'),
(4096,'ARITH_ABORT'),
(8192,'NUMERIC_ROUNDABORT'),
(16384,'DATEFIRST'),
(32768,'DATEFORMAT'),
(65536,'LanguageID'),
(131072,'UPON'),
(262144,'ROWCOUNT')
select top 1 @setoptionfound = isnull([value], -1),
@result = isnull([option] , '') + '; '
from @setoptionslist
where [value] <= @setoptions
order by [value] desc
return @result +
case when @setoptionfound > -1 then
dbo.fn_querystoresetoptions(@setoptions-@setoptionfound)
else ''
end
end
go
select dbo.fn_QueryStoreSetOptions(CAST(set_options as int)) as set_options_list, *
from sys.query_context_settings;
/*****************************************************************************************
Demo 3 - Case 3
Identify Failed Plan Forcing
Antonios Chatzipavlis
SQLschool.gr
*****************************************************************************************/
use AdventureWorks;
go
select query_id , count(plan_id)
from sys.query_store_plan
group by query_id
having count(plan_id) > 1;
-- tracked queries
select *
from sys.query_store_plan
where query_id = 785;
alter database AdventureWorks set automatic_tuning ( FORCE_LAST_GOOD_PLAN = ON );
exec sp_query_store_force_plan @query_id = 785, @plan_id = 2168;
select plan_id,
force_failure_count,
last_force_failure_reason
from sys.query_store_plan;
go
create event session [qs_forcing_plan_failures] on server
add event qds.query_store_plan_forcing_failed
add target package0.ring_buffer with
(
max_memory=4096 kb,
event_retention_mode=allow_single_event_loss,
max_dispatch_latency=30 seconds,
max_event_size=0 kb,
memory_partition_mode=none,
track_causality=off,
startup_state=on
);