大网数据广告活动
数据库与应用安全

High CPU or memory grants when using optimized nested loop or batch sort

· 52CE 我爱测

Provides a resolution for high CPU and memory usage when you run queries with optimized nested loop or batch sort operators.

This article helps you resolve the problem where high CPU usage occurs when you run queries in SQL Server.

Applies to:   SQL Server
Original KB number:   2009160

Symptoms

When you operate Microsoft SQL Server that has a highly concurrent workload, you may notice some performance issues in queries. This behavior may exhibit as medium to high CPU usage or extreme memory grant requests.

You may also experience other side effects, such as OOM conditions, memory pressure for plan cache eviction, or unexpected RESOURCE_SEMAPHORE waits.

Additionally, you may notice that query plans for queries that consume lots of CPU or memories have the OPTIMIZED attribute for a Nested Loops join operator set to True.

Cause

This issue may occur in some cases where SQL Server query processor introduces an optional sort operation to improve performance. This operation is known as an "Optimized Nested Loop" or "Batch Sort" and the query optimizer determines when to best introduce these operators. In rare cases, the query touches only a few rows, but the setup cost for the sort operation is so significant that the cost of the optimized nested loop outweighs its benefits. Therefore, in those cases you may observe slower performance compared to what is expected.

Resolution

Trace flag 2340

To fix the issue, use trace flag 2340 to disable the optimization. Trace flag 2340 instructs the query processor not to use a sort operation (batch sort) for optimized nested loop joins when generating a query plan. This affects the entire instance.

Before you enable this trace flag, you can test your applications thoroughly to make sure that you get the expected performance benefits when you disable this optimization. This is because the sort optimization can be helpful when there is a large increase in the number of rows that are touched by the plan.

For more information, see DBCC TRACEON - Trace Flags (Transact-SQL).

Modify code to use the DISABLE_OPTIMIZED_NESTED_LOOP hint

Alternatively, apply the following DISABLE_OPTIMIZED_NESTED_LOOP query hint to disable the optimization at the query level.

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

For more information, see DISABLE_OPTIMIZED_NESTED_LOOP.

More information

Database Engine Service Startup Options

Applies to

  • SQL Server 2005 through SQL Server 2019

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 12/23/2021
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。