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

Symptoms
When too many concurrent inserts occur in the same hash bucket or the ad hoc SQL Server plan cache hits 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 2012 or SQL Server 2014.
Cause
The issue occurs because, when the SQL Server plan cache hits its entry limit, plans with low cost must be evicted to insert new plans. This leads to 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 white paper Plan Cache Internals. The maximum number of entries that a plan cache can hold is four times of the buckets count. You can verify this information by using 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 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. When you have a workload that is using different ad hoc queries, this limit can become a bottleneck. In these cases, you can use the change in this hotfix. 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. Therefore, the plan cache now can hold a maximum number 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.
Properties

Article ID: 3026083 - Last Review: 06/22/2015 15:29:00 - Revision: 4.0

Microsoft SQL Server 2012 Service Pack 2, Microsoft SQL Server 2012 Service Pack 1, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2014 Service Pack 1

  • kbqfe kbfix kbsurveynew kbexpertiseadvanced KB3026083
Feedback