Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

High CPU use occurs when you run queries in SQL Server


View products that this article applies to.

Symptoms

When you operate a server that is running Microsoft SQL Server and that has a highly concurrent workload, you notice some performance issues in which queries contribute significantly to high CPU use 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 memory have the OPTIMIZED attribute for a Nested Loops join operator set to True.

↑ Back to the top


Cause

This issue occurs in some cases because SQL Server query processor introduces a sort operation for optimization, although it is not required. This operation is known as an Optimized Nested Loops or Batch Sort.

In these cases, the plan touches only a smaller number of rows, and the setup cost for the sort operation may outweigh its benefits. Therefore, it causes poor performance.

↑ Back to the top


Resolution

To fix the issue, use trace flag 2340 to disable the optimization. Alternatively, to disable the optimization at the query level, apply the following query hint:

USE HINT (DISABLE_OPTIMIZED_NESTED_LOOP) 

Before you use 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 very helpful when there is a large increase in the number of rows that are touched by the plan.

↑ Back to the top


More information

For more information about how to enable a trace flag as a startup option, see Database Engine Service Startup Options

↑ Back to the top


Keywords: kbfix, kb, kbqfe

↑ Back to the top

Article Info
Article ID : 2009160
Revision : 16
Created on : 7/23/2019
Published on : 7/23/2019
Exists online : False
Views : 383