Query Store脚本收集

绩隐金 2023-02-21

各种书上，博客上，收集的有关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'%<MissingIndexes>%'
    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

文章转载自绩隐金，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Query Store脚本收集

评论