印刻数据库

外观

SQLServer Query Store分析 ​

SQLServer Query Store是SQLServer 2016及以上版本引入的一项重要功能，用于监控、分析和优化查询性能。它自动收集查询执行的详细信息，包括执行计划、执行统计数据和查询等待信息，帮助DBA快速识别和解决性能问题。

Query Store概述 ​

1. 什么是Query Store ​

Query Store是一个内置的查询性能监控工具，它：

  • 自动收集查询执行计划和统计数据
  • 保留历史查询性能数据
  • 允许比较不同时间段的查询性能
  • 支持强制使用特定执行计划
  • 提供图形化和T-SQL两种访问方式

2. Query Store的主要功能 ​

功能描述
执行计划捕获自动捕获查询执行计划，包括计划变更
性能统计收集收集查询执行的CPU、内存、I/O和执行时间等统计数据
等待统计分析收集查询的等待类型和等待时间
计划回归检测检测执行计划变更导致的性能回归
计划强制允许强制使用特定的执行计划
性能趋势分析提供查询性能随时间变化的趋势
自动 tuning 建议基于历史数据提供查询优化建议

3. Query Store的架构 ​

Query Store由三个主要组件组成：

  • 捕获组件：负责捕获查询执行计划和统计数据
  • 存储组件：将捕获的数据存储在数据库的系统表中
  • 报告组件：提供图形化和T-SQL接口访问查询数据

Query Store数据存储在数据库的专用系统表中，这些表位于sys.query_store_*架构下。

Query Store配置 ​

1. 启用Query Store ​

使用SSMS启用Query Store：

  1. 打开SQL Server Management Studio(SSMS)
  2. 连接到SQLServer实例
  3. 展开「数据库」节点，右键点击目标数据库，选择「属性」
  4. 在「属性」对话框中，选择「Query Store」页
  5. 将「操作模式(要求重启)」设置为「只读」或「读写」
  6. 配置其他选项（如数据刷新间隔、保留期等）
  7. 点击「确定」应用更改

使用T-SQL启用Query Store：

sql
-- 启用Query Store
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200,
    WAIT_STATS_CAPTURE_MODE = ON
);
GO

2. 主要配置选项 ​

配置选项描述默认值
OPERATION_MODEQuery Store操作模式：READ_WRITE或READ_ONLYOFF
CLEANUP_POLICY旧数据清理策略STALE_QUERY_THRESHOLD_DAYS = 30
DATA_FLUSH_INTERVAL_SECONDS数据刷新到磁盘的间隔(秒)900
INTERVAL_LENGTH_MINUTES统计数据聚合间隔(分钟)60
MAX_STORAGE_SIZE_MBQuery Store最大存储大小(MB)100
QUERY_CAPTURE_MODE查询捕获模式：ALL、AUTO、CUSTOM或NONEAUTO
SIZE_BASED_CLEANUP_MODE基于大小的清理模式：AUTO或OFFAUTO
MAX_PLANS_PER_QUERY每个查询保存的最大计划数200
WAIT_STATS_CAPTURE_MODE等待统计捕获模式：ON或OFFOFF (SQL Server 2017+)

3. 配置最佳实践 ​

  • OPERATION_MODE：生产环境建议使用READ_WRITE模式
  • CLEANUP_POLICY：根据业务需求设置，建议保留30-90天
  • DATA_FLUSH_INTERVAL_SECONDS：建议设置为900-3600秒，平衡性能和数据安全性
  • INTERVAL_LENGTH_MINUTES：建议设置为60分钟，适合大多数场景
  • MAX_STORAGE_SIZE_MB：根据数据库大小和查询量设置，建议至少1GB
  • QUERY_CAPTURE_MODE：建议使用AUTO模式，自动捕获相关查询
  • SIZE_BASED_CLEANUP_MODE：建议使用AUTO模式，自动管理存储空间
  • MAX_PLANS_PER_QUERY：建议设置为100-200，避免过多计划占用存储空间
  • WAIT_STATS_CAPTURE_MODE：建议启用，便于分析查询等待情况

Query Store使用方法 ​

1. 使用SSMS访问Query Store ​

SSMS提供了图形化的Query Store报告界面：

  1. 打开SSMS，连接到SQLServer实例
  2. 展开「数据库」节点，右键点击目标数据库
  3. 选择「Reports」→「Standard Reports」→「Query Store」
  4. 选择所需的报告类型：
    • 总体资源消耗
    • 顶级资源消耗查询
    • 查询等待统计信息
    • 跟踪查询
    • 强制计划
    • 回归查询
    • 总体资源消耗趋势
    • 近期查询活动

2. 使用T-SQL查询Query Store数据 ​

Query Store提供了一系列系统视图，用于访问查询数据：

系统视图描述
sys.query_store_query包含查询的基本信息
sys.query_store_plan包含执行计划的信息
sys.query_store_runtime_stats包含查询执行的统计数据
sys.query_store_runtime_stats_interval包含统计数据聚合间隔信息
sys.query_store_wait_stats包含查询等待统计信息
sys.query_store_query_text包含查询文本

示例查询：

sql
-- 查询资源消耗最高的前10个查询
SELECT TOP 10
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.avg_logical_io_writes,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.count_executions,
    p.last_compile_start_time
FROM 
    sys.query_store_query_text AS qt
JOIN 
    sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN 
    sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN 
    sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
JOIN 
    sys.query_store_runtime_stats_interval AS rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE 
    rsi.start_time > DATEADD(day, -7, GETDATE())
ORDER BY 
    rs.avg_cpu_time DESC;

Query Store在性能监控中的应用 ​

1. 识别性能回归 ​

Query Store可以检测执行计划变更导致的性能回归。通过比较不同执行计划的性能指标，可以快速定位问题：

使用SSMS查找回归查询：

  1. 打开「回归查询」报告
  2. 设置时间范围和性能指标（如CPU时间、持续时间等）
  3. 查看回归查询列表
  4. 点击具体查询，查看执行计划变更和性能差异

使用T-SQL查找回归查询：

sql
-- 查找执行计划变更导致性能下降的查询
WITH QueryStats AS (
    SELECT 
        q.query_id,
        p.plan_id,
        qt.query_sql_text,
        rs.avg_duration / 1000.0 AS avg_duration_ms,
        rs.count_executions,
        ROW_NUMBER() OVER (PARTITION BY q.query_id ORDER BY p.plan_id) AS plan_num
    FROM 
        sys.query_store_query_text AS qt
    JOIN 
        sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
    JOIN 
        sys.query_store_plan AS p ON q.query_id = p.query_id
    JOIN 
        sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
    JOIN 
        sys.query_store_runtime_stats_interval AS rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE 
        rsi.start_time > DATEADD(day, -7, GETDATE())
)
SELECT 
    qs1.query_sql_text,
    qs1.plan_id AS old_plan_id,
    qs1.avg_duration_ms AS old_avg_duration_ms,
    qs2.plan_id AS new_plan_id,
    qs2.avg_duration_ms AS new_avg_duration_ms,
    (qs2.avg_duration_ms - qs1.avg_duration_ms) / qs1.avg_duration_ms * 100 AS duration_increase_percent
FROM 
    QueryStats AS qs1
JOIN 
    QueryStats AS qs2 ON qs1.query_id = qs2.query_id AND qs1.plan_num + 1 = qs2.plan_num
WHERE 
    qs2.avg_duration_ms > qs1.avg_duration_ms * 1.5 -- 性能下降超过50%
ORDER BY 
    duration_increase_percent DESC;

2. 强制执行计划 ​

当查询性能回归时，可以使用Query Store强制使用之前的良好执行计划：

使用SSMS强制执行计划：

  1. 打开「跟踪查询」报告
  2. 查找目标查询
  3. 右键点击良好的执行计划
  4. 选择「强制计划」
  5. 确认强制操作

使用T-SQL强制执行计划：

sql
-- 强制使用特定执行计划
EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;
GO

-- 查看强制的计划
SELECT 
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    p.is_forced
FROM 
    sys.query_store_query_text AS qt
JOIN 
    sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN 
    sys.query_store_plan AS p ON q.query_id = p.query_id
WHERE 
    p.is_forced = 1;

3. 分析查询等待统计 ​

Query Store可以捕获查询的等待统计信息，帮助识别I/O、锁、内存等等待问题：

使用SSMS查看等待统计：

  1. 打开「查询等待统计信息」报告
  2. 设置时间范围
  3. 查看等待类型分布
  4. 点击具体等待类型，查看相关查询

使用T-SQL查询等待统计：

sql
-- 查询等待时间最长的查询
SELECT TOP 10
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    ws.wait_category_desc,
    ws.wait_time_ms / ws.execution_count AS avg_wait_time_ms,
    ws.execution_count
FROM 
    sys.query_store_query_text AS qt
JOIN 
    sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN 
    sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN 
    sys.query_store_wait_stats AS ws ON p.plan_id = ws.plan_id
JOIN 
    sys.query_store_runtime_stats_interval AS rsi ON ws.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE 
    rsi.start_time > DATEADD(day, -7, GETDATE())
ORDER BY 
    ws.wait_time_ms DESC;

4. 监控查询性能趋势 ​

Query Store可以帮助监控查询性能随时间的变化趋势：

示例查询：

sql
-- 查询特定查询的性能趋势
DECLARE @query_id INT = 123;

SELECT 
    rsi.start_time,
    AVG(rs.avg_cpu_time / 1000.0) AS avg_cpu_ms,
    AVG(rs.avg_duration / 1000.0) AS avg_duration_ms,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads
FROM 
    sys.query_store_runtime_stats AS rs
JOIN 
    sys.query_store_runtime_stats_interval AS rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE 
    rs.query_id = @query_id
    AND rsi.start_time > DATEADD(month, -1, GETDATE())
GROUP BY 
    rsi.start_time
ORDER BY 
    rsi.start_time;

Query Store最佳实践 ​

1. 配置最佳实践 ​

  • 合理设置存储大小：根据数据库大小和查询量，设置合适的MAX_STORAGE_SIZE_MB
  • 调整数据刷新间隔：根据业务需求调整DATA_FLUSH_INTERVAL_SECONDS
  • 设置适当的保留期：根据合规要求和性能分析需求，设置STALE_QUERY_THRESHOLD_DAYS
  • 选择合适的查询捕获模式：
    • AUTO：自动捕获相关查询（推荐）
    • ALL：捕获所有查询（适合详细分析）
    • NONE：不捕获查询（适合只读模式）

2. 性能影响考虑 ​

  • Query Store会对数据库性能产生一定影响，建议在生产环境中监控其性能开销
  • 对于高负载系统，可以考虑将Query Store设置为只读模式，或调整捕获模式
  • 定期清理旧数据，避免Query Store占用过多存储空间

3. 数据管理 ​

  • 定期查看Query Store的存储使用情况
  • 当存储接近上限时，考虑调整清理策略或增加存储大小
  • 对于不再需要的查询数据，可以手动清理：
sql
-- 清理Query Store数据
ALTER DATABASE [YourDatabase]
SET QUERY_STORE CLEAR;
GO

-- 清理特定查询的数据
EXEC sys.sp_query_store_remove_query @query_id = 123;
GO

-- 清理特定计划的数据
EXEC sys.sp_query_store_remove_plan @plan_id = 456;
GO

4. 与其他工具结合使用 ​

  • 将Query Store与Extended Events结合使用，深入分析查询性能
  • 将Query Store数据导出到专用监控系统，进行长期趋势分析
  • 结合Database Engine Tuning Advisor，获取查询优化建议

5. 监控Query Store健康状态 ​

  • 定期检查Query Store的操作模式，确保其处于正常状态
  • 监控Query Store的性能开销，避免对生产系统造成影响
  • 检查强制计划的状态，确保强制计划仍然有效

Query Store版本差异 ​

SQLServer版本Query Store特性
2016引入基本Query Store功能
2017新增等待统计捕获、自动计划校正、增强的计划强制功能
2019新增Intelligent Query Processing集成、增强的自动tuning功能、改进的计划回归检测
2022新增Query Store hints、增强的等待统计分析、改进的内存管理
Azure SQL DB支持所有Query Store特性，包括自动tuning
Azure SQL 托管实例支持所有Query Store特性

常见问题(FAQ) ​

Q1: Query Store对性能有影响吗？ ​

A: 是的，Query Store会对数据库性能产生一定影响，主要体现在：

  • 捕获查询数据会消耗CPU和内存资源
  • 写入数据会产生I/O开销
  • 存储数据会占用磁盘空间

影响程度取决于查询量、配置选项和系统负载。建议在生产环境中监控Query Store的性能开销。

Q2: Query Store数据会丢失吗？ ​

A: Query Store数据存储在数据库的系统表中，当数据库备份和恢复时，Query Store数据会一起备份和恢复。但在以下情况下，Query Store数据可能会丢失：

  • 手动清理Query Store数据
  • 数据库被重建或重新初始化
  • Query Store操作模式从读写切换到关闭

Q3: 如何处理Query Store存储空间不足的问题？ ​

A: 当Query Store存储空间不足时，可以采取以下措施：

  1. 增加MAX_STORAGE_SIZE_MB设置
  2. 缩短STALE_QUERY_THRESHOLD_DAYS保留期
  3. 调整查询捕获模式为AUTO或NONE
  4. 手动清理旧数据
  5. 考虑将Query Store设置为只读模式

Q4: 强制计划后性能没有改善怎么办？ ​

A: 如果强制计划后性能没有改善，可能的原因包括：

  • 数据分布发生了变化
  • 统计信息过时
  • 索引结构发生了变化
  • 强制的计划不再适合当前数据

建议：

  1. 更新统计信息
  2. 重新编译查询
  3. 考虑使用新的执行计划
  4. 分析查询逻辑，进行查询重写

Q5: 如何迁移Query Store数据？ ​

A: Query Store数据存储在数据库中，当数据库迁移时，Query Store数据会一起迁移。如果需要单独迁移Query Store数据，可以：

  1. 备份包含Query Store数据的数据库
  2. 恢复到目标服务器
  3. 确保目标服务器版本支持Query Store
  4. 验证Query Store数据完整性

Q6: Query Store支持Azure SQL DB吗？ ​

A: 是的，Azure SQL DB完全支持Query Store功能，包括：

  • 自动tuning（自动计划校正）
  • 增强的性能报告
  • 与Azure Monitor集成
  • 支持所有Query Store配置选项

Query Store案例分析 ​

案例一：执行计划回归导致的性能问题 ​

场景：某电商网站在促销期间，订单查询响应时间突然从100ms增加到500ms。

诊断过程：

  1. 使用Query Store的「回归查询」报告，发现订单查询的执行计划发生了变化
  2. 比较新旧执行计划，发现新计划不再使用高效的索引，而是进行了全表扫描
  3. 查看查询历史，发现执行计划变更是由于统计信息更新导致的

解决方案：

  1. 使用Query Store强制使用之前的良好执行计划
  2. 更新表的统计信息，确保统计信息准确
  3. 添加必要的索引，优化查询性能
  4. 监控Query Store，确保性能稳定

结果：订单查询响应时间恢复到100ms以下，系统性能恢复正常。

案例二：识别CPU密集型查询 ​

场景：数据库服务器CPU使用率持续超过80%，需要找出消耗CPU最多的查询。

诊断过程：

  1. 使用Query Store的「顶级资源消耗查询」报告，按CPU消耗排序
  2. 发现一个报表查询消耗了大量CPU资源
  3. 分析该查询的执行计划，发现其使用了低效的嵌套循环连接

解决方案：

  1. 重写查询，优化连接逻辑
  2. 添加必要的索引，减少扫描操作
  3. 将查询拆分为多个小查询，降低单次查询的CPU消耗
  4. 调整查询的执行频率，避免在高峰期执行

结果：CPU使用率降至50%以下，系统性能得到显著提升。

总结 ​

SQLServer Query Store是一个强大的查询性能监控和分析工具，它可以自动收集查询执行计划和统计数据，帮助DBA快速识别和解决性能问题。通过合理配置和使用Query Store，可以：

  • 检测执行计划回归，快速恢复良好的执行计划
  • 识别资源消耗最高的查询，进行针对性优化
  • 分析查询等待统计，识别I/O、锁、内存等瓶颈
  • 监控查询性能趋势，预测未来性能问题
  • 与其他工具结合使用，深入分析查询性能

在使用Query Store时，需要注意其对性能的影响，合理配置各项参数，定期管理数据，并监控其健康状态。通过掌握Query Store的使用方法和最佳实践，可以显著提高SQLServer数据库的性能管理水平，确保业务系统的高效运行。