FIX: SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014

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

Symptoms


Assume that you have some queries referencing tables that have many statistics in Microsoft SQL Server 2012 or Microsoft SQL Server 2014. When too many concurrent inserts occur in the same hash bucket or the system table rowset cache hits its entry limit of 1,196 with large bucket lengths, severe contention on a SOS_CACHESTORE spinlock and a high CPU usage occur.

Cause


This issue occurs because, when you constantly insert entries for the same system table into this system table rowset cache during compilation, you may encounter contention on the SOS_CACHESTORE spinlock that protects the hash table buckets for this cache. One of the conditions that causes this issue is lots of statistics presenting on the tables that are involved in the compiled query.

Resolution


Service pack information

To resolve this issue, obtain Service Pack 1 for SQL Server 2014.

For more information about SQL Server 2014 Service Pack 1 (SP1), see bugs that are fixed in SQL Server 2014 Service Pack 1 .

Cumulative Update information

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

More Information


Each database has a system table rowset cache that stores entries from system tables in memory. When information is read from the system tables, it is cached into the system table rowset cache for that database. The hash bucket that determines the location of the cached entry is originally based on the system table identifier. When you read lots of entries at the same time from the same system table, it may result in the previous contention.
The changes in the fix address the contention in two ways:
  • The size of each system table rowset cache hash table is increased from 299 to 599.
  • The hashing algorithm is changed in a way that entries from the same system table do not always end up in the same hash bucket.
You can view the information about the system table rowset cache by using the following queries:
select * 
from sys.dm_os_memory_cache_hash_tables
where name IN ( 'SystemRowsetStore' )

select *
from sys.dm_os_memory_cache_counters
where name IN ( 'SystemRowsetStore' )
You can view the information about the spinlock contention by using the following query:
select * from sys.dm_os_spinlock_stats
where name = 'SOS_CACHESTORE'

Status


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