FIX: Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server

Applies to: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard More

Symptoms


Consider the following scenario:
  • You use an instance of Microsoft SQL Server 2012 or SQL Server 2014 that has NUMA enabled.
  • SQL Server is configured to use many CPU cores across multiple NUMA nodes (for example, 4 NUMA nodes that have 12 cores each).
  • You run a compile-heavy workload that generates a high compilation rate from multiple concurrent connections.
In this scenario, you do not notice any increase in the results of a compilation/sec query with growing number of available cores beyond 12 or so. Also, CPU may show 100 percent utilization.

Note The severity of the CPU saturation depends on the number of cores per NUMA node and also on your compilation rate.

Cause


The issue occurs because SQL Server uses a data structure that may contribute to CPU contention patterns on NUMA-enabled hardware across concurrent compilations.

Resolution


After you apply this fix, SQL Server replaces the use of a shared structure with thread-local data.

The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 1 for SQL Server 2012 SP2

Cumulative Update 2 for SQL Server 2014

Cumulative Update 10 for SQL Server 2012 SP1

Workaround


To work around the issue, reduce the compilation rate in your SQL Server system.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.