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

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

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.

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_tableswhere name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' ) select name, type, pages_kb, entries_count from sys.dm_os_memory_cache_counterswhere 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.