探索查询存储

  • 13 分钟

SQL Server 查询存储是一项按数据库的功能，可自动捕获查询、计划和运行时统计信息的历史记录，从而简化性能故障排除和查询优化。 它还提供有关数据库使用模式和资源消耗的见解。

查询存储由三个存储组成：

  • 计划存储：存储估计的执行计划信息。
  • 运行时统计信息存储：存储执行统计信息信息。
  • 等待统计信息存储：保留等待统计信息。

查询存储组件的屏幕截图。

启用查询存储

Azure SQL 数据库中默认已启用查询存储。 若要将查询存储与 SQL Server 和 Azure Synapse Analytics 配合使用，首先需要启用它。 若要启用查询存储功能，请使用以下对环境有效的查询：

-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;

查询存储如何收集数据

查询存储在多个阶段与查询处理管道集成。 在每个集成点，都会在内存中收集数据，并异步写入磁盘，以最大程度地减少 I/O 开销。 集成点如下所述：

  1. 当某个查询首次执行时，其查询文本和初始估计的执行计划将发送到查询存储并持久保存。

  2. 查询重新编译时，计划在查询存储中更新。 如果重新编译导致生成新的执行计划，则新计划也会保存在查询存储中以补充以前的计划。 此外，查询存储会跟踪每个查询计划的执行统计信息，以便进行比较。

  3. 在编译和检查重新编译阶段，查询存储将识别是否对要执行的查询实施了强制计划。 如果查询存储提供的强制计划与过程缓存中的计划不同，则重新编译查询。

  4. 当某个查询执行时，其运行时统计信息将保存在查询存储中。 查询存储聚合此数据以确保准确表示每个查询计划。

查询执行管道中显示为流程图的查询存储集成点的屏幕截图。

若要详细了解查询存储如何收集数据，请参阅查询存储如何收集数据。

常见方案

SQL Server 查询存储提供对数据库作性能的宝贵见解。 常见方案包括：

  • 由于选择低查询执行计划，识别和修复性能回归。
  • 识别和优化资源消耗量最高的查询。
  • A/B 测试以评估数据库和应用程序更改的影响。
  • 确保 SQL Server 升级后的性能稳定性。
  • 确定最常用的查询。
  • 审核查询的查询计划的历史记录。
  • 识别和改进计划外工作负荷。
  • 了解数据库的普遍等待类别以及影响等待时间的参与查询和计划。
  • 根据资源消耗（CPU、I/O、内存）分析一段时间内的数据库使用模式。

发现查询存储视图

在数据库上启用查询存储后，将在对象资源管理器中显示数据库的相应查询存储文件夹。 对于 Azure Synapse Analytics，查询存储视图显示在系统视图下。 查询存储视图提供 SQL Server 数据库性能方面的聚合快速见解。

SSMS 对象资源管理器的屏幕截图，其中突出显示了查询存储视图。

退化的查询

由于执行计划更改，回归查询在一段时间内的性能下降。 由于各种因素（包括架构更改、统计信息更改和索引更改）估计的执行计划可能会更改。 调查过程缓存可能是第一个本能，但它只存储查询的最新执行计划，并且可以根据系统的内存需求逐出计划。 但是，查询存储会为每个查询保留多个执行计划，从而灵活地通过 计划强制 解决因计划更改导致的查询性能回归而强制选择特定计划。

“回归查询”视图可以查明在指定时间范围内由于执行计划发生更改而导致执行指标回归的查询。 此视图允许根据所选指标（例如持续时间、CPU 时间、行计数等）和统计信息（总计、平均值、最小值、最大值或标准偏差）进行筛选。 然后，它会根据提供的筛选器列出前 25 个回归查询。 默认情况下，将显示查询的图形条形图视图，但可以选择以网格格式查看查询。

从左上角查询窗格中选择查询后，计划摘要窗格会显示与查询随时间推移关联的持久查询计划。 在“计划摘要”窗格中选择查询计划会显示底部窗格中的图形查询计划。 通过计划摘要窗格和图形查询计划窗格中的工具栏按钮，可以强制所选查询的所选计划。 此窗格结构和行为在所有 SQL 查询视图中始终使用。

显示每个不同窗格的查询存储回归查询视图的屏幕截图。

或者，你可以通过 sp_query_store_force_plan 存储过程来使用计划强制。

EXEC sp_query_store_force_plan @query_id=73, @plan_id=79

总体资源消耗

在“资源总消耗量”视图中可以分析指定时间范围内多个执行指标（例如执行计数、持续时间、等待时间等）的资源总消耗量。 呈现的图表是交互式的；从其中一个图表选择某个度量时，会在新选项卡中显示一个钻取视图，其中显示了与所选度量关联的查询。

SQL 查询存储总体资源消耗视图的屏幕截图，其中包含一个配置对话框，用于指示可显示的不同指标。

详细信息视图提供与所选指标相关的前 25 个资源消耗者查询。 此详细信息视图使用一致的界面，可在其中检查关联的查询及其详细信息，评估已保存的估计查询计划，并选择性地使用计划强制来提高性能。 当系统资源争用造成问题时（例如，当 CPU 使用率达到容量限制时），此视图很有作用。

数据库中消耗量排名前 25 的资源的屏幕截图。

资源消耗量最大的几个查询

“资源消耗量排名靠前的查询”视图类似于“资源总消耗量”视图的向下钻取详细信息。 它还允许选择指标和统计信息作为筛选器。 但是，其中显示的查询是根据所选筛选器和时间范围列出的影响度最高的 25 个查询。

数据库中排名靠前的资源消耗查询视图的屏幕截图。

“最消耗资源查询”视图在识别和改进计划外工作负荷时，提供工作负荷计划外性质的第一个指示。 例如，下图中选择了“执行计数”指标和“总计”统计信息，从中可以看出，大约 90% 的资源消耗量排名靠前的查询只执行了一次。

按执行计数筛选的排名靠前的资源消耗查询的屏幕截图。

具有强制计划的查询

在“带强制计划的查询”视图中可以快速查看附带强制查询计划的查询。 如果强制计划不再按预期方式执行并需要重新评估，则此视图会有作用。 在此视图中可以查看选定查询的所有已保存的估计执行计划，从而轻松判断另一个计划现在是否更适合用于提高性能。 如果是，则可以根据需要使用工具栏按钮来取消强制执行某个计划。

带有强制执行计划的查询的屏幕截图。

变化程度高的查询

查询性能可能因执行不同而异。 “差异较大的查询”视图包含其所选指标的差异或标准偏差最高的查询的分析。 该界面与大多数查询存储视图一致，允许检查查询详细信息、评估执行计划和选择性地强制执行特定的计划。 使用此视图可将不可预测的查询优化为更一致的性能模式。

具有高度差异的查询的屏幕截图。

查询等待统计信息

“查询等待统计信息”视图分析数据库的最活跃等待类别并呈现图表。 此图表是交互式的；选择一个等待类别可以钻取到与等待时间统计信息相关的查询的详细信息。

具有高度差异的查询视图的屏幕截图。

详细信息视图界面也与大多数查询存储视图一致，允许检查查询详细信息、评估执行计划和选择性地强制执行特定的计划。 此视图可帮助识别影响不同应用程序中用户体验的查询。

跟踪查询

在“跟踪查询”视图中可以根据输入的查询 ID 值分析特定的查询。 运行后，该视图将提供查询的完整执行历史记录。 某个执行附带勾选标记表示使用了强制计划。 此视图可以提供查询见解（例如带强制计划的查询），以验证查询性能是否保持稳定。

按特定查询 ID 筛选的跟踪查询视图的屏幕截图。

使用查询存储查找查询等待

当系统的性能开始下降时，有用的做法是查看查询等待统计信息，以求识别原因。 除了识别需要调整的查询之外，它还可以阐明可能有益的基础结构升级。

SQL 查询存储通过“查询等待统计信息”视图来提供数据库的主要等待类别的见解。 目前有 23 种等待类别。

当你打开“查询等待统计信息”视图时，条形图会显示数据库的影响度最大的等待类别。 此外，使用等待类别窗格的工具栏中的筛选器可以根据总等待时间（默认值）、平均等待时间、最小等待时间、最大等待时间或标准偏差等待时间来计算等待统计信息。

“查询等待统计信息”视图的屏幕截图，其中以条形图形式显示了最有影响力的类别。

选择等待类别可钻取到参与该等待类别的查询的详细信息。 在此视图中，可以调查影响度最大的各个查询。 可以通过在查询窗格中选择一个查询，来访问“计划摘要”窗格中显示的已保存的估计执行计划。 从“计划摘要”窗格中选择查询计划会在底部窗格中显示图形查询计划。 在此视图中，可以针对查询强制执行或取消强制执行查询计划以提高性能。

“查询等待统计信息”视图的屏幕截图，其中显示了对等待类别影响最大的查询。

自动计划更正

SQL Server 2017 和 Azure SQL 数据库引入了通过分析查询存储中的数据进行自动计划更正的概念。 在 SQL Server 2017（或更高版本）和 Azure SQL 数据库中启用数据库查询存储时，SQL Server 引擎会查找查询计划回归并提供建议。 可在 sys.dm_db_tuning_recommendations 动态管理视图 (DMV) 中查看这些建议。 这些建议包含 T-SQL 语句，以便在性能处于良好状态时手动强制执行查询计划。

如果对这些建议有信心，则可以使 SQL Server 在遇到衰退时自动强制执行计划。 使用 ALTER DATABASE 和 AUTOMATIC_TUNING 参数启用自动计划更正。

对于 Azure SQL 数据库，还可通过 Azure 门户或 REST API 中的自动优化选项启用自动计划更正。 对于启用了查询存储的任何数据库，自动计划更正建议始终处于启用状态（这是 Azure SQL 数据库和 Azure SQL 托管实例的默认设置）。 对于新数据库，Azure SQL 数据库在默认情况下会启用自动计划更正 (FORCE_PLAN)。