使用查询存储来监视性能
适用于：
SQL Server 2016（13.x）及更高版本Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics（仅限专用 SQL 池）Microsoft Fabric 中的 SQL 数据库
查询存储功能提供有关 SQL Server、Azure SQL 数据库、Fabric SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics 的查询计划选择和性能的见解。 查询存储可帮助你快速找到查询计划更改所造成的性能差异，从而简化性能疑难解答。 查询存储将自动捕获查询、计划和运行时统计信息的历史记录，并保留它们以供查阅。 它按时间窗口将数据分割开来，使你可以查看数据库使用模式并了解服务器上何时发生了查询计划更改。
可以使用 ALTER DATABASE SET 选项配置查询存储。
- 有关在 Azure SQL 数据库中运行查询存储的信息，请参阅 在 Azure SQL 数据库中运行查询存储。
- 若要详细了解如何使用查询存储发现可操作信息并优化性能，请参阅使用查询存储优化性能。
- 若要详细了解如何在不更改应用程序代码的情况下调整查询计划，请参阅查询存储提示。
重要
如果仅对 SQL Server 2016 (13.x) 中正在运行的工作负载见解使用查询存储，请尽快安装 KB 4340759 中的性能可伸缩性修补程序。
启用查询存储
- 默认将为新的 Azure SQL 数据库和 Azure SQL 托管实例数据库启用查询存储。
- 默认不会为 SQL Server 2016 (13.x)、SQL Server 2017 (14.x)、SQL Server 2019 (15.x) 启用查询存储。 对于从 SQL Server 2022 (16.x) 开始的新数据库，默认情况下在
READ_WRITE模式下启用它。 若要启用功能以更好地跟踪性能历史记录、排查查询计划相关问题并在 SQL Server 2022 (16.x) 中启用新功能，建议在所有数据库上启用查询存储。 - 默认不为新的 Azure Synapse Analytics 数据库启用查询存储。
使用 SQL Server Management Studio 中的“查询存储”页面
在对象资源管理器中，右键单击数据库，然后选择“属性”。
注意
至少需要 16 版本的 Management Studio。
在“数据库属性” 对话框中，选择“查询存储” 页。
在“操作模式(要求)”对话框中，选择“读写”。
使用 Transact-SQL 语句
使用ALTER DATABASE语句为给定数据库启用查询存储。 例如：
ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
在 Fabric SQL 数据库中ALTER DATABASE配置查询存储的选项目前受到限制。
在 Azure Synapse Analytics 中，无需其他选项即可启用查询存储，例如：
ALTER DATABASE <database_name>
SET QUERY_STORE = ON;
有关与查询存储相关的语法选项的详细信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。
注意
无法为 master 或 tempdb 数据库启用查询存储。
重要
有关启用查询存储并使其适用于你的工作负载，请参阅查询存储最佳做法。
查询存储中的信息
由于统计信息更改、架构更改、索引的创建/删除等多种不同原因，SQL Server 中任何特定查询的执行计划通常会随着时间而改进。过程缓存（其中存储了缓存的查询计划）仅存储最近的执行计划。 还会由于内存压力从计划缓存中逐出计划。 因此，执行计划更改造成的查询性能回归可能非常重大，且长时间才能解决。
由于查询存储会保留每个查询的多个执行计划，因此它可以强制执行策略，以引导查询处理器对某个查询使用特定执行计划。 这称为“计划强制”。 查询存储中的计划强制是通过使用类似于 USE PLAN 查询提示的机制来提供的，但它不需要在用户应用程序中进行任何更改。 计划强制可在非常短的时间内解决由计划更改造成的查询性能回归。
注意
查询存储收集 DML 语句的计划，例如SELECT、INSERT、UPDATE、DELETE、MERGE 和 BULK INSERT。
根据设计，查询存储不会收集 CREATE INDEX 等 DDL 语句的计划。查询存储通过收集基础 DML 语句的计划来捕获累积资源消耗。 例如，查询存储可能会显示在内部执行的 SELECT 和 INSERT 语句以填充新索引。
默认情况下，查询存储不对本机编译的存储过程收集数据。 使用 sys.sp_xtp_control_query_exec_stats 为本机编译的存储过程启用数据收集。
等待统计信息是有助于排除数据库引擎中的性能问题的另一信息来源。 长期以来，等待统计信息仅适用于实例级别，难以回溯到特定查询。 从 SQL Server 2017（14.x）和 Azure SQL 数据库开始，查询存储包含一个跟踪等待统计信息的维度。下面的示例允许查询存储收集等待统计信息。
ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );
使用查询存储功能的常见方案为：
- 快速查找并修复通过强制使用先前查询计划而造成的计划性能回归。 修复近期由于执行计划更改而出现性能回归的查询。
- 确定在给定时间窗口中查询执行的次数，从而帮助 DBA 对性能资源问题进行故障排除。
- 标识过去 x 小时内的前 n 个查询（按执行时间、内存占用等）。
- 审核给定查询的查询计划历史记录。
- 分析特定数据库的资源（CPU、I/O 和内存）使用模式。
- 确定资源上正在等待的前 n 个查询。
- 了解特定查询或计划的等待性质。
查询存储包含三个存储：
- 计划存储：用于保存执行计划信息。
- 运行时统计信息存储：用于保存执行统计信息。
- 等待统计信息存储：用于保存等待统计信息。
max_plans_per_query 配置选项限制了计划存储中查询可存储的唯一计划数。 为增强性能，通过异步方式向存储写入信息。 为尽量减少空间使用量，将在按固定时间窗口上聚合运行时统计信息存储中的运行时执行统计信息。 可通过查询查询存储目录视图来查看这些存储中的信息。
以下查询从查询存储中返回有关查询、其计划、编译时间和运行时间统计资料的信息。
SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;
次要副本的查询存储
适用于：SQL Server（SQL Server 2022 (16.x) 及更高版本）
次要副本的查询存储功能在次要副本工作负载上启用可用于主要副本的相同的查询存储功能。 启用次要副本的查询存储后，副本会将通常存储在查询存储中的查询执行信息发送回主要副本。 然后，主要副本会将数据保存到自身查询存储中的磁盘。 从本质上讲，主要副本和所有次要副本之间共享有一个查询存储。 查询存储存在于主要副本上，将所有副本的数据一起存储。
有关次要副本的查询存储的完整信息，请参阅 Always On 可用性组次要副本的查询存储。
使用回归查询功能
在启用查询存储后，刷新对象资源管理器窗格的数据库部分，以添加“查询存储”部分。
注意
对于 Azure Synapse Analytics，查询存储视图位于对象资源管理器窗格数据库部分的“系统视图”下。
选择“回归查询”，以在 SQL Server Management Studio 中打开“回归查询”窗格。 “回归查询”窗格显示查询存储中的查询和计划。 使用顶部的下拉列表框，根据各种条件筛选查询：持续时间 (ms)（默认）、CPU 时间 (ms)、逻辑读取 (KB)、逻辑写入 (KB)、物理读取 (KB)、CLR 时间 (ms)、DOP、内存消耗 (KB)、行计数、已用日志内存 (KB)、已用临时 DB 内存 (KB) 和等待时间 (ms)。
选择某个计划以查看图形查询计划。 可以使用按钮查看源查询、强制执行和取消强制执行查询计划、在网格和图表格式之间进行切换、比较所选的计划（如果选择多个）及刷新显示。
若要强制执行某一计划，请选择查询和计划，然后选择“强制计划”。 你只可以强制执行由查询计划功能保存且仍保留在查询计划缓存中的计划。
查找正在等待的查询
从 SQL Server 2017 (14.x) 和 Azure SQL 数据库开始，查询存储中提供了一段时间内每个查询的等待统计信息。
在查询存储中，等待类型将合并到等待类别中。 sys.query_store_wait_stats (Transact-SQL) 中提供从等待类别到等待类型的映射。
在 SQL Server Management Studio 18.0 或更高版本中，选择“查询等待统计信息”以打开“查询等待统计信息”窗格。 “查询等待统计信息”窗格显示包含查询存储中排在前面的等待类别的条形图。 使用顶部的下拉列表选择等待时间的聚合条件：平均值、最大值、最小值、标准偏差和总计（默认）。
通过选择条形图和所选等待类别展示的详细信息视图，选择等待类别。 这个新的条形图包含对该等待类别有贡献的查询。
使用顶部的下拉列表框，根据各种等待时间条件为所选等待类别筛选查询：平均值、最大值、最小值、标准偏差和总计（默认）。 选择某个计划以查看图形查询计划。 可使用按钮来查看源查询，强制执行和取消强制执行某一查询计划，以及刷新显示内容。
等待类别可将不同等待类型按性质合并为类似的桶。 不同的等待类别需要不同的后续分析才能解决此问题，但相同类别的等待类型可引起非常相似的故障排除体验，并假定基于等待的受影响的查询会成为用于成功完成大部分此类调查所缺少的部分。
下面的示例介绍如何在查询存储中引入等待类别前后更深入了解工作负荷：
| 曾经的体验 | 新的体验 | 操作 |
|---|---|---|
| 每个数据库的高 RESOURCE_SEMAPHORE 等待 | 特定查询在查询存储中的高内存等待 | 在查询存储中查找消耗内存最多的查询。 这些查询可能会延迟受影响查询的进度。 请考虑对这些查询或受影响的查询使用 MAX_GRANT_PERCENT 查询提示。 |
| 每个数据库的高 LCK_M_X 等待 | 特定查询在查询存储中的高锁定等待 | 检查受影响查询的查询文本，并确定目标实体。 在查询存储中查找修改同一实体的其他查询，该实体频繁执行和/或具有较高持续时间。 确定这些查询后，请考虑更改应用程序逻辑以提高并发性，或使用限制较少的隔离级别。 |
| 每个数据库的高 PAGEIOLATCH_SH 等待 | 特定查询在查询存储中的高缓冲 IO 等待 | 在查询存储中查找具有大量物理读取的查询。 如果它们与含较高 IO 等待的查询匹配，执行执行搜索而不是扫描时，请考虑引入关于基础实体的索引，以便减少查询的 IO 开销。 |
| 每个数据库的高 SOS_SCHEDULER_YIELD 等待 | 特定查询在查询存储中的高 CPU 等待 | 查找查询存储中前几个使用 CPU 最多的查询。 其中，请确定其高 CPU 趋势与受影响查询的高 CPU 等待关联的查询。 重点优化这些查询 - 可能存在计划回归，或缺失的索引。 |
配置选项
有关配置查询存储参数的可用选项，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。
查询 sys.database_query_store_options 视图以确定查询存储的当前选项。 有关值的详细信息，请参阅 sys.database_query_store_options。
有关使用 Transact-SQL 语句来设置配置选项的示例，请参阅选项管理。
注意
对于 Azure Synapse Analytics，可以像其他平台一样启用查询存储，但不支持其他配置选项。
相关视图、函数和过程
通过 Management Studio 或使用以下视图和过程来查看和管理查询存储。
查询存储函数
此函数有助于执行查询存储操作。
查询存储目录视图
目录视图提供了查询存储的相关信息。
查询存储的存储过程
存储过程配置了查询存储。
sp_query_store_consistency_check (Transact-SQL)1
1 在极端情况下，查询存储可能由于内部错误而进入 ERROR 状态。 从 SQL Server 2017 (14.x) 开始，如果出现这种情况，可通过在受影响的数据库内执行 sp_query_store_consistency_check 存储过程来恢复查询存储。 请参阅 sys.database_query_store_options，了解 actual_state_desc 列说明中所述的详细信息。
查询存储维护
本文扩展了有关查询存储维护和管理的最佳做法和建议：管理查询存储的最佳做法。
性能审核和疑难解答
有关深入了解如何使用查询存储来优化性能的详细信息，请参阅使用查询存储优化性能。
其他性能主题：