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.
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:
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.
