SQL Server 2005 och 2008 har en användbar funktion kallad default trace. Den är påslagen…
On a 64 bit system the number of buckets for the SQL Server plan cache is 40 009 and the plan cache can hold four times the bucket count. With the default size of the plan cache SQL Server can only hold 160 036 execution plans.
If you have a workload that uses many different ad hoc queries, this limit can become a bottleneck, causing heavy contention for the SOS_CACHESTORE spinlock that provides synchronization for the hash table buckets of the SQL Server plan cache. This will cause a high CPU usage.
To solve the issue you need to enable the SQL Server Trace flag 174 at startup to increases the bucket count to 160,001 on 64-bit systems.
The affected versions are:
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
You need to have at least the CU:s listed below to be able to activate the startup trace flag 174 by adding “-T 174” as a startup parameter in SQL Server configuration manager.
Cumulative Update 1 for SQL Server 2014 SP1
Cumulative Update 5 for SQL Server 2012 SP2
Cumulative Update 6 for SQL Server 2014
Cumulative Update 14 for SQL Server 2012 SP1
You can verify the bucket size with these queries:
select name, type, buckets_countfrom sys.dm_os_memory_cache_hash_tableswhere name IN ( ‘SQL Plans’ , ‘Object Plans’ , ‘Bound Trees’ );
select name, type, pages_kb, entries_countfrom sys.dm_os_memory_cache_counterswhere name IN ( ‘SQL Plans’ , ‘Object Plans’ , ‘Bound Trees’ );
Read more about the issue at KB3026083 – FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server
Read more about other trace flags at Updated: Microsoft SQL Server Trace Flag list