SQL Server 2022 查询存储：启用、分析与性能问题定位

1. 查询存储概述

查询存储（Query Store）是 SQL Server 的核心功能，持续捕获查询执行计划、运行时统计信息（如 CPU 时间、I/O 操作）和历史性能数据。通过建立性能基线，可快速定位回归问题。

2. 启用查询存储

-- 针对单个数据库启用
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

-- 配置参数（可选）
ALTER DATABASE [YourDatabase] SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE,      -- 读写模式
  DATA_FLUSH_INTERVAL_SECONDS = 900, -- 数据刷盘间隔（秒）
  INTERVAL_LENGTH_MINUTES = 60,      -- 统计聚合间隔
  MAX_STORAGE_SIZE_MB = 1024,        -- 最大存储空间
  QUERY_CAPTURE_MODE = AUTO          -- 捕获模式：ALL/AUTO/NONE
);

关键参数说明

• QUERY_CAPTURE_MODE：
  • ALL：捕获所有查询
  • AUTO：智能过滤低频查询（推荐）
  • NONE：仅捕获强制保留的查询
• 存储建议：生产环境至少分配 1GB 存储空间。

3. 核心数据分析方法

(1) 查询性能仪表板

在 SSMS 中右键数据库 → Reports → Query Store → Regressed Queries，可视化展示：

• 执行时间/资源消耗突增的查询
• 计划变更导致的性能退化
• 资源占用 Top 10 查询

(2) 关键 DMV 查询

-- 检索性能退化的查询
SELECT * FROM sys.query_store_runtime_stats 
WHERE execution_type = 0  -- 常规执行
ORDER BY avg_duration DESC;

-- 分析执行计划变更
SELECT 
  plan_id, 
  query_id, 
  CAST(query_plan AS XML) AS plan_xml
FROM sys.query_store_plan
WHERE query_id = @YourQueryId;

4. 性能问题定位实战

场景 1：执行计划突变

现象：查询突然变慢，但无代码变更。
解决步骤：

1. 在 Tracked Queries 报告中定位查询
2. 对比历史执行计划（SSMS 图形化界面）
3. 强制恢复稳定计划：

   EXEC sp_query_store_force_plan @query_id, @plan_id;
   

场景 2：参数嗅探问题

现象：相同查询有时快有时慢。
诊断方法：

-- 检查同一查询的不同执行计划
SELECT 
  plan_id, 
  count_executions,
  avg_compile_duration
FROM sys.query_store_runtime_stats
WHERE query_id = @ProblemQueryId
GROUP BY plan_id;

解决方案：

• 使用 OPTION(RECOMPILE) 提示
• 或启用参数敏感计划优化（SQL Server 2022 新增功能）

场景 3：资源瓶颈定位

-- 识别 CPU 消耗 Top 查询
SELECT TOP 10
  query_id,
  SUM(avg_cpu_time * count_executions) AS total_cpu
FROM sys.query_store_runtime_stats
GROUP BY query_id
ORDER BY total_cpu DESC;

5. 最佳实践

1. 监控策略：
   • 定期检查 query_store_storage_size 避免空间耗尽
   • 设置 CLEANUP_POLICY 自动清理过期数据
2. 问题诊断：
   • 结合 wait_stats 分析阻塞类型（如 PAGEIOLATCH 指示 I/O 瓶颈）
3. 版本优势：
   • SQL Server 2022 新增 Query Store Hints，无需修改代码即可注入优化提示
4. 灾备恢复：
   • 使用 ALTER DATABASE...SET QUERY_STORE CLEAR 重置问题状态

6. 注意事项

• 启用后轻微增加系统开销（约 3-5%），建议在维护窗口操作
• 避免在 tempdb 或只读副本启用
• 使用 QUERY_CAPTURE_POLICY 精细控制捕获规则（2022 新增）

总结：查询存储通过持续性能数据采集，将问题诊断从"事后追溯"升级为"实时分析"。结合 SQL Server 2022 的增强功能（如智能计划修复），可显著缩短故障恢复时间。