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