在需要时获得性能见解,同时在无需更改代码的情况下提高性能

已完成

SQL Server 2022 提供内置功能来缩短查询优化的时间,包括查询存储和下一代智能查询处理 (IQP) 功能,可帮助你在无需更改代码的情况下获得更快且更一致的性能。

查询优化的挑战

开发人员和 SQL 专家同意,虽然某些查询只是正常工作,但对于需要性能故障排除和查询优化练习的查询,可能会出现一些性能情况。 优化查询性能可能是一个成本高昂且通常耗时的过程。

使用查询存储进行查询优化的解决方案

查询存储是存储在用户数据库中的一组内置查询性能统计信息。 查询存储将自动捕获查询、计划和运行时统计信息的历史记录,并保留此信息以供查阅。 它按时间窗口将数据分割开来,使你可以查看数据库使用模式并了解服务器上何时发生了查询计划更改。 有关详细信息,请参阅使用查询存储监视性能

SQL Server 2022 包括查询存储的重要增强功能,以缩短查询优化的时间。

默认情况下启用查询存储

在 SQL Server 2022 之前,必须使用 T-SQL ALTER DATABASE 语句启用查询存储。 对于 SQL Server 2022,默认情况下,创建的任何新数据库都将启用查询存储。 从以前版本的 SQL Server 还原的数据库将保留备份数据库时捕获的查询存储设置。 自 SQL Server 2016 推出以来,查询存储具有多项增强功能,允许用户在不显著影响应用程序性能的情况下启用查询存储。 此外,查询存储中提供了几个新设置,使用户能够更轻松地控制如何捕获和清理查询性能信息。 用户可以随时使用 T-SQL ALTER DATABASE 语句禁用查询存储。

查询存储提示

查询存储提示提供了一种易于使用的方法,可在不更改应用程序代码的情况下制定查询计划。 可以执行存储在查询存储中的任何查询,并使用系统存储过程来应用查询提示。 查询提示会影响查询计划,目的是在不更改应用程序代码的情况下提高查询性能。 例如,可以应用查询存储提示,要求查询在不更改查询文本的情况下使用特定的 MAXDOP 值。

查询存储提示不应用作优化查询性能的正常步骤,但可以用作查询优化的有用工具,尤其是在无法更改应用程序中的查询文本时。 此外,一些新的智能查询处理功能使用查询存储提示。 可以在 sys.query_store_query_hints 目录视图中查看任何持久化查询存储提示。 有关详细信息,请参阅查询存储提示

只读副本的查询存储

尽管查询存储有利于缩短优化查询或轻松确定查询性能问题所需的时间,但性能信息仅适用于针对 AlwaysOn 可用性组中的主要副本执行的查询。 在 SQL Server 2022 中,可以通过 T-SQL ALTER DATABASE 语句使用新选项,使查询存储能够收集对次要副本执行的只读查询的性能信息。 所有副本的所有性能信息都保存在主副本上。 查询存储中捕获新信息,以指示哪个副本与查询或查询计划相关联。

注意

需要跟踪标志 12606 才能为次要副本启用查询存储。

智能查询处理的查询存储

尽管查询存储收集查询的关键性能信息,但 SQL Server 2022 中的查询处理器也将使用查询存储来保存信息以提高查询性能。 这些功能包括强制执行优化计划、内存授予反馈、基数估算 (CE) 模型反馈,以及并行度 (DOP) 反馈。

使用下一代智能查询处理提高性能的解决方案

智能查询处理 (IQP) 是内置于数据库引擎的查询处理器中的一系列功能,旨在无需更改代码即可提高性能。 下一代智能查询处理基于 SQL Server 2017 和 2019 中找到的功能,如下图所示:

Diagram of the Intelligent Query Processing feature family.

如你所见,已经有几个属于 SQL Server 2017 和 SQL Server 2019 的 IQP 功能。 SQL Server 2022 为 IQP 添加了多项新功能。 可以在 SQL 数据库中的智能查询处理中随时了解 IQP 功能的所有最新动态。 让我们仔细看看其中每个新功能。

数据库引擎使用两个原则来制定智能查询处理的决策:

  • 使用新方法或自动化避免导致任何查询性能回归。
  • 在数据库或查询级别提供方法以禁用特定的 IQP 功能。 可以选择要在数据库或查询级别启用的 IQP 功能,同时根据数据库兼容性级别使用其他 IQP 功能。

升级到 SQL Server 2022 后的功能

如果升级到 SQL Server 2022,则有新功能可以独立于数据库的数据库兼容性级别来提高性能。 即使需要使用以前版本的 SQL Server 的数据库兼容性级别,兼容级别也允许你利用新功能。 有关详细信息,请参阅兼容性认证

近似百分位函数

SQL Server 包括两个 Transact-SQL (T-SQL) 函数,可帮助分析工作负载计算值范围的百分位数:

  • PERCENTILE_CONT
  • PERCENTILE_DISC

SQL Server 2022 提供与以下两个函数等效的近似函数:

  • APPROX_PERCENTILE_CONT
  • APPROX_PERCENTILE_DISC

近似百分位函数对于具有异常大数据集的分析工作负载可能很有用。 这些函数的执行速度更快,实现可保证在 99% 的概率内最多 1.33% 的错误率。

优化计划强制

强制执行优化计划是 SQL Server 2022 中的一项新功能,旨在缩短在查询存储中强制执行查询计划时编译某些查询所需的时间。

某些查询就其本质而言可能需要大量的时间来编译。 强制执行优化计划提供缩短编译查询所需的时间的方法,即通过在查询存储编译步骤中将查询存储为具有查询计划中强制执行的查询计划的合格查询。 通过强制执行查询计划,可以锁定特定查询的查询计划。 下次需要编译已启用强制执行优化计划的查询时,编译步骤用于显著加快执行查询的编译阶段。

有关详细信息,请参阅使用查询存储优化计划强制

使用数据库兼容性级别 140 或更高的 SQL Server 2022 IQP 功能

如果使用数据库兼容性级别 140 或更高,则可以获取更多智能查询处理功能来增强 SQL Server 2022 中的内存授予反馈。 SQL Server 2017(批处理模式)和 SQL Server 2019(行模式)中引入了内存授予反馈。 内存授予反馈是一种机制,即查询处理器将从执行反馈中学习,以调整内存授予以便进一步执行,从而避免或减少 tempdb 溢出和 RESOURCE_SEMAPHORE 等待。

内存授予反馈百分位数

在 SQL Server 2022 之前,内存授予反馈基于特定查询的最新执行。 这可能会导致某些情况下出现不同的反馈调整,从而可能导致查询处理器禁用特定查询的内存授予反馈。 在 SQL Server 2022 中,在使用内存授予反馈之前,内存授予反馈使用百分位数法查看多个执行中的内存授予。

内存授予反馈持久化

在 SQL Server 2022 之前,内存授予反馈仅存储在内存中的缓存计划中。 如果缓存计划被逐出,则必须在新查询执行时重新计算内存授予反馈。 在启用了查询存储的 SQL Server 2022 中,内存授予反馈将保存在查询存储中。 可以从 sys.query_store_plan_feedback 目录视图中查看内存授予反馈持久化。

有关详细信息,请参阅内存授予反馈

使用数据库兼容性级别 160 或更高的 SQL Server 2022 IQP 功能

如果使用数据库兼容性级别 160 或更高,则可以获取更多智能查询处理功能,例如参数敏感计划优化、基数估算的增强功能和并行度。

参数敏感计划优化

编译查询时,生成的执行计划会考虑存储过程中额查询或参数化查询中使用的任何参数的值。 此概念称为参数探查。 对于存储过程或参数化查询中的语句,缓存中只能存在一个查询计划。 在大多数情况下,这不会导致应用程序出现任何性能问题。 但是,在某些情况下,基于参数为查询检索的数据可能会倾斜或分布不均匀。 在这些情况下,对于不同的参数值,单个缓存计划可能不是最佳计划。 此问题称为“参数敏感计划”

在 SQL Server 2022 中,优化器可以检测参数敏感计划方案,并为同一存储过程或参数化查询缓存多个计划。 优化器使用称为“查询变体”的概念来聚合参数值集,以匹配最适合这些参数值的查询计划。

有关详细信息,请参阅参数敏感计划优化

基数估算 (CE) 反馈

在数据库兼容性级别为 120 的 SQL Server 2014 中,Microsoft 开始使用查询处理器中的新模型,对某些查询模式的基数估算做出某些假设。 在某些情况下,新模型生成了更正确的查询计划,但可能会导致性能比旧 CE 模型更慢。 CE 模型方案包括相关性、联接包含和行目标。 自 SQL Server 2014 以来,已包含多个选项,用于在数据库级别或查询级别将旧 CE 模型或控制 CE 行为与跟踪标志或查询提示结合使用。

在启用了查询存储的 SQL Server 2022 中,优化器将评估高度重复的查询,这些查询与模型可能做出错误假设的 CE 模型方案的模式相匹配。 然后,优化器将尝试测试并验证是否可以使用查询提示来加快查询执行速度。 验证性能提高后,查询提示就会保存在查询存储中,用于将来的查询执行。 可以在 sys.query_store_query_hints 目录视图中查看任何应用的 CE 反馈查询提示,并在 sys.query_store_plan_feedback 目录视图中查看 CE 反馈详细信息。 不会使用 CE 反馈的情况:已启用旧版 CE 模型、已在查询存储中强制使用查询计划或者查询具有现有查询存储提示。

有关详细信息,请参阅基数估计

并行度 (DOP) 反馈

在某些情况下,SQL Server 中的优化器将通过多个并发线程使用并行来运行称为运算符的查询计划部分。 用于查询计划运算符的线程数称为并行度 (DOP)。 SQL Server 可以使用服务器、数据库、资源组或称为最大并行度 (MAXDOP) 的查询设置来控制每个运算符的最大线程数。 为 SQL Server 部署设置正确的 MAXDOP 可能是一项复杂的练习,有时可能会十分困难。

在 SQL Server 2022 中,优化器可以使用称为 DOP 反馈的技术来查找查询的并行效率。 并行效率是查询的最小 DOP,它会导致相同的总体查询持续时间(重构常见等待)。 减少查询的 DOP 可以为其他查询或应用程序提供更多线程和 CPU 资源。

DOP 反馈要求启用查询存储,将数据库兼容性级别设置为 160,并打开名为 DOP_FEEDBACK 的数据库设置。 借助这些设置,优化器将与查询存储后台任务协调工作,以查找可受益于较低 DOP 的重复且长时间运行的查询。 反馈周期将用于验证调整后的查询持续时间(重构等待)不会以较低的 DOP 值回归,并且观察到查询的总体 CPU 较低。 经过一段时间的验证后,较低的 DOP 被视为稳定,并将保存在查询存储中。 优化器将继续以逐步向下的方式验证较低的 DOP 值,以找到最佳并行效率或最低 DOP(即 2)。 DOP 反馈永远不会增加 DOP,并且会遵循查询的 MAXDOP 设置,具体取决于已应用的任何服务器、数据库、资源调控器或查询提示。

DOP 反馈不需要重新编译,但将在任何新的查询编译中检查验证。 可以在 sys.query_store_plan_feedback 目录视图中观察持久化 DOP 反馈值。 可以使用动态管理视图 sys.dm_exec_query_stats 和 sys.query_store_runtime_stats 目录视图中的 last_dop 列查看用于查询的最新 DOP。

有关详细信息,请参阅并行度 (DOP) 反馈

知识检查

1.

查询存储提示提供哪些功能?

2.

参数敏感计划优化有助于哪种方案?