各种书上，博客上，收集的有关Query Store 查询存储的脚本，
收集控
---- Check Disk Usage Status And Other Settings
---- 查看使用空间，以及其他QueryStore设置
select * from sys.database_query_store_options
---- Check Auto Tuning Status
---- 自动Tuning的情况
SELECT ddtr.type,
ddtr.reason,
ddtr.last_refresh,
ddtr.state,
ddtr.score,
ddtr.details
FROM sys.dm_db_tuning_recommendations AS ddtr;
---- Check Used RAM Summary
---- 内存使用情况
SELECT
type,
sum(pages_kb) AS [MemoryUsed_KB],
sum(pages_kb)/1024 AS [MemoryUsed_MB]
FROM sys.dm_os_memory_clerks
WHERE type like '%QDS%'
or type like '%QueryDiskStore%'
GROUP BY type
ORDER BY type;
---- Check Top 10 Run Duration Scripts
---- Top 10 跑的最久的代码
SELECT TOP 10 qt.query_sql_text
,q.query_id
,so.name
,so.type
,SUM(rs.count_executions * rs.avg_duration) AS 'Total Duration'
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
INNER JOIN sysobjects so ON so.id = q.object_id
WHERE rsi.start_time >= DATEADD(DAY, - 10, GETUTCDATE())
GROUP BY qt.query_sql_text
,q.query_id
,so.name
,so.type
ORDER BY SUM(rs.count_executions * rs.avg_duration) DESC
---- Check Query Plan
---- 一堆查询计划
SELECT top 500
[qsq].[query_id],
[qsp].[plan_id],
[qsq].[object_id],
[qsq].[query_hash],
[rs].[count_executions],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
[qst].[query_sql_text],
TRY_CONVERT(XML, [qsp].[query_plan]) AS [QueryPlan_XML]
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]
---- Missing Index
---- 索引缺失的查询
SELECT
SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
SUM(qrs.count_executions) AS sum_executions,
AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
SUM(qsq.count_compiles) AS sum_compiles,
(SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,
TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
WHERE qsp2.query_id=qsq.query_id
ORDER BY qsp2.plan_id DESC)) AS query_plan,
qsq.query_id,
qsq.query_hash
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
qsp.query_plan like N'%
and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC
---- 查询等待时间
SELECT TOP (10)
[ws].[wait_category_desc],
[ws].[avg_query_wait_time_ms],
[ws].[total_query_wait_time_ms],
[ws].[plan_id],
[qt].[query_sql_text],
[rsi].[start_time],
[rsi].[end_time]
FROM [sys].[query_store_query_text] [qt]
JOIN [sys].[query_store_query] [q]
ON [qt].[query_text_id] = [q].[query_text_id]
JOIN [sys].[query_store_plan] [qp]
ON [q].[query_id] = [qp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
ON [qp].[plan_id] = [rs].[plan_id]
JOIN [sys].[query_store_runtime_stats_interval] [rsi]
ON [rs].[runtime_stats_interval_id] = [rsi].[runtime_stats_interval_id]
JOIN [sys].[query_store_wait_stats] [ws]
ON [ws].[runtime_stats_interval_id] = [rs].[runtime_stats_interval_id]
AND [ws].[plan_id] = [qp].[plan_id]
WHERE [rsi].[end_time] > DATEADD(MINUTE, -60, GETUTCDATE())
AND [ws].[execution_type] = 0
ORDER BY [ws].[avg_query_wait_time_ms] DESC;
-- 带有最近一次的Plan ID和文本的参数化查询
select qsq.query_id,
max(qsqt.query_sql_text) query_sql_text,
max(qsp.plan_id) plan_id,
max(qsrs.max_duration) max_duration,
max(qsrs.max_cpu_time) max_cpu_time,
min(qsrs.min_cpu_time) min_cpu_time,
min(qsrs.min_duration) min_duration,
max(qsrs.stdev_duration) stdev_duration,
max(qsrs.stdev_cpu_time) stdev_cpu_time
from sys.query_store_query qsq,
sys.query_store_query_text qsqt,
sys.query_store_plan qsp,
sys.query_store_runtime_stats qsrs
where qsq.query_text_id= qsqt.query_text_id
and qsp.query_id=qsq.query_id
and qsrs.plan_id=qsp.plan_id
and (qsq.query_parameterization_type<>0
or qsqt.query_sql_text like '%@%')
and qsq.is_internal_query=0
and qsqt.query_sql_text not like '%sys.%'
and qsqt.query_sql_text not like '%sys[ ].%'
and qsqt.query_sql_text not like '%@[sys@].%' escape '@'
and qsqt.query_sql_text not like '%INFORMATION_SCHEMA%'
and qsqt.query_sql_text not like '%msdb%'
and qsqt.query_sql_text not like '%master%'
and qsp.last_execution_time=(select max(last_execution_time)
from sys.query_store_plan qsp2
where qsp2.query_id= qsp.query_id)
group by qsq.query_id
order by stdev_cpu_time desc
--数据库中10个最近被执行的查询
SELECT
TOP 10 qt.query_sql_text, q.query_id, qt.query_text_id,
p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC
--获取每个查询的执行次数
SELECT
q.query_id, qt.query_text_id,
qt.query_sql_text,
SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC
--在最近一个小时内，平均执行时间最长的10个查询
SELECT
TOP 10 qt.query_sql_text,
q.query_id, qt.query_text_id, p.plan_id,
GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time, rs.avg_duration
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.avg_duration desc
--最近24小时内，10个平均物理I/O 读最高的查询
SELECT
TOP 10 qt.query_sql_text,
q.query_id, qt.query_text_id,
p.plan_id, rs.runtime_stats_id,
rsi.start_time, rsi.end_time,
rs.avg_physical_io_reads,
rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads desc
--最近性能倒退（回归）的查询，条件是过去48小时内执行时间增长了一倍以上）
SELECT
qt.query_sql_text,
q.query_id,
p1.plan_id AS plan1,
rs2.avg_duration AS plan2
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p1 ON q.query_id = p1.query_id
JOIN sys.query_store_runtime_stats rs1 ON p1.plan_id = rs1.plan_id
JOIN sys.query_store_runtime_stats_interval rsi1 ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
JOIN sys.query_store_plan p2 ON q.query_id = p2.query_id
JOIN sys.query_store_runtime_stats rs2 ON p2.plan_id = rs2.plan_id
JOIN sys.query_store_runtime_stats_interval rsi2 ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(HOUR, -48, GETUTCDATE()) AND
rsi2.start_time > rsi1.start_time AND
rs2.avg_duration > 2*rs1.avg_duration
--具有多个执行计划的查询
WITH QueryWithMultiplePlans
AS
(
SELECT COUNT(*) AS cnt, q.query_id
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
GROUP BY q.query_id HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id, OBJECT_NAME(object_id) AS ContainingObject,
query_sql_text, plan_id, p.query_plan AS plan_xml, p.last_compile_start_time,
p.last_execution_time
FROM QueryWithMultiplePlans qm
JOIN sys.query_store_query q ON qm.query_id = q.query_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
ORDER BY query_id, plan_id