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

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_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 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: Jun 22, 2015 - Revision: 1

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

Feedback