optimize for ad hoc workloads（服务器配置选项）

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Analytics Platform System (PDW)

“针对即席工作负荷进行优化” 选项用于提高包含许多一次性临时批处理的工作负荷计划缓存的效率。 如果该选项设置为 1，则数据库引擎将在首次编译批处理时在计划缓存中存储一个编译的小计划存根，而不是存储完全编译的计划。 此选项不会让未重复使用的编译计划填充计划缓存，从而可能有助于缓解内存压力。 但是，启用此选项可能会影响对一次性计划进行故障排除的能力。

编译的计划存根允许数据库引擎识别此临时批次是在之前编译的，仅存储编译的计划存根。 再次调用此批准（已编译或已执行）时，数据库引擎编译该批次，从计划缓存中删除编译的计划存根，并将完全编译的计划添加到计划缓存。

可以通过查询 sys.dm_exec_cached_plans 目录视图并在 cacheobjtype 列中查找“已编译计划”来查找编译计划存根。 存根具有唯一 plan_handle。 编译计划存根没有与其关联的执行计划，并且查询计划句柄不会返回图形或 XML 显示计划。

跟踪标志 8032 将缓存限制参数还原为 SQL Server 2005 (9.x) RTM 设置，此设置通常允许更大的缓存。 当频繁重复使用的缓存条目不适合缓存时，以及当“针对即席工作负荷进行优化”选项未能解决与计划缓存相关的问题时，请使用此设置。

警告

如果大缓存使较少的内存可用于其他内存消耗者（如缓冲池），则跟踪标志 8032 可能导致性能较差。

注解

将“针对即席工作负荷进行优化”选项设置为 1 只会影响新计划；已在计划缓存中的计划不受影响。

若要立即影响已缓存的查询计划，需使用 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 清除计划缓存，或需重启 SQL Server。

建议

避免在计划缓存中拥有大量一次性计划。 常见原因包括：

  • 不统一定义查询参数的数据类型。 这尤其适用于字符串的长度，但可应用到具有最大长度、精度或规模的任何数据类型。 例如，如果名为 @Greeting 的参数在一次调用时传递为 nvarchar(10) 并在下次调用时传递为 nvarchar(20)，则将为每个参数大小创建各自的计划。

  • 未参数化的查询。 如果查询具有一个或多个参数，其中硬编码的值将提交到数据库引擎，则每个查询可能存在大量的查询计划。 计划可能因所使用的查询参数数据类型和长度的每种组合而存在。

如果一次性计划的数量在 OLTP 服务器的 SQL Server 数据库引擎内存中占了很大一部分（并且这些计划是临时计划），请使用这个服务器选项降低这些对象的内存使用量。

如果已启用“针对即席工作负荷进行优化”选项，则无法查看单一使用查询的执行计划，因为只缓存了计划存根。 根据环境和工作负荷，可以从以下两项功能中受益：

  • SQL Server 2016 (13.x) 中推出的查询存储功能可帮助你快速查找因查询计划有变导致的性能差异。 默认情况下，在 SQL Server 2022（16.x）及更高版本中的新数据库上启用查询存储。

  • 强制参数化通过降低查询编译和重新编译的频率，可以提高某些数据库的性能。 能够通过强制参数化受益的数据库通常是需要处理来自源（例如，销售点应用程序）的大量并发查询的数据库。

    由于参数敏感度，强制参数化可能会导致性能问题。 有关详细信息，请参阅调查并解决参数敏感问题。 SQL Server 2022（16.x）及更高版本还可以启用参数敏感计划优化。

示例

要获取一次性缓存计划的数量，请运行下列查询：

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

相关内容