KB3026083 - FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server

Applies To

SQL Server 2012 Developer SQL Server 2012 Enterprise SQL Server 2012 Standard SQL Server 2014 Enterprise - duplicate (do not use) SQL Server 2014 Developer - duplicate (do not use) SQL Server 2014 Standard - duplicate (do not use) SQL Server 2014 Web - duplicate (do not use) SQL Server 2016 Developer - duplicate (do not use) SQL Server 2016 Enterprise - duplicate (do not use) SQL Server 2016 Enterprise Core - duplicate (do not use) SQL Server 2016 Standard - duplicate (do not use) SQL Server 2017 Developer SQL Server 2017 Enterprise SQL Server 2017 Enterprise Core

Symptoms

When too many concurrent inserts occur in the same hash bucket, or the ad hoc SQL Server plan cache reaches its entry limit of 160,036, severe contention on SOS_CACHESTORE spinlock occurs. In this situation, a high CPU usage occurs in Microsoft SQL Server.

Cause

The issue occurs when the SQL Server plan cache reaches its entry limit, and plans that have low cost must be evicted in order to insert new plans. This causes heavy contention for the SOS_CACHESTORE spinlock that provides synchronization for the hash table buckets of the SQL Server plan cache.

Resolution

Cumulative Update information

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

About cumulative updates for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

More Information

The plan cache has two limits: The total size and the total number of all plans. The size and entry count limits are explained in the following white paper:

Plan Cache Internals

The maximum number of entries that a plan cache can hold is four times the bucket count. You can verify this information by running the following queries:


select name, type, buckets_count 
from sys.dm_os_memory_cache_hash_tables
where name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' )
 
select name, type, pages_kb, entries_count 
from sys.dm_os_memory_cache_counters
where name IN ( 'SQL Plans' , 'Object Plans' ,  'Bound Trees' )

For example, on 64-bit systems, the number of buckets for the SQL Server plan cache is 40,009. Therefore, the maximum number of entries that can fit inside the SQL Server plan cache is 160,036.

If you have a workload that uses different ad hoc queries, this limit can become a bottleneck. The change that is made by this hotfix addresses this situation. After you install this hotfix and enable the change by using the startup trace flag "-T 174," the bucket count is increased to 160,001 on 64-bit systems. The plan cache is then able to hold a maximum of 640,004 plans.

Status

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