使用优化嵌套循环或批处理排序的查询可能会造成 CPU 或内存授予过高

  • 项目

本文可帮助你解决在 SQL Server 中运行查询时 CPU 使用率过高的问题。

适用于： SQL Server
原始 KB 数： 2009160

症状

操作具有高度并发工作负荷的 Microsoft SQL Server时，你可能会注意到查询中的一些性能问题。 此行为可能显示为中到高 CPU 使用率或极端内存授予请求。

还可能会遇到其他副作用，例如 OOM 条件、计划缓存逐出时内存压力或意外 RESOURCE_SEMAPHORE 等待。

此外，你可能会注意到，使用大量 CPU 或内存的查询的查询计划将 Nested Loops 联接运算符的 OPTIMIZED 属性设置为 True。

原因

在某些情况下，SQL Server查询处理器引入可选的排序操作以提高性能时，可能会出现此问题。 此操作称为“优化嵌套循环”或“批处理排序”，查询优化器确定何时最好地引入这些运算符。 在极少数情况下，查询只涉及几行，但排序操作的设置成本非常巨大，因此优化嵌套循环的成本大于其优势。 因此，在这些情况下，可能会发现性能低于预期。

解决方案

跟踪标志 2340

若要解决此问题，请使用跟踪标志 2340 禁用优化。 跟踪标志 2340 指示查询处理器在生成查询计划时不要对优化嵌套循环联接使用排序操作 (批处理排序) 。 这会影响整个实例。

在启用此跟踪标志之前，可以全面测试应用程序，以确保在禁用此优化时获得预期的性能优势。 这是因为，当计划涉及的行数大幅增加时，排序优化会很有用。

有关详细信息，请参阅 DBCC TRACEON - 跟踪标志 (Transact-SQL) 。

修改代码以使用DISABLE_OPTIMIZED_NESTED_LOOP提示

或者，应用以下 DISABLE_OPTIMIZED_NESTED_LOOP 查询提示在查询级别禁用优化。

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT (DISABLE_OPTIMIZED_NESTED_LOOP));

有关详细信息，请参阅 DISABLE_OPTIMIZED_NESTED_LOOP。

更多信息

数据库引擎服务启动选项

适用对象

  • SQL Server 2005 年到 2019 SQL Server