Last week on SQLPerformance.com I blogged about ad hoc workloads and how they impact performance, noting plan cache bloat, and I had a comment asking about SQL Server plan cache limits. Kimberly mentions them in her classic post Plan cache and optimizing for adhoc workloads, but I thought I’d put the information into a blog post because I always have trouble finding the original references: Plan Caching in SQL Server 2008 (which is still accurate for SQL Server 2019, as far as I know) and Plan Cache Internals.
There are two limitations related to the plan cache:
- Number of entries
- Total size in MB (based on server memory)
By default, the plan cache is limited to 160,036 total entries (40,009 entries per bucket), and size based on max server memory (for SQL Server 2008+ and SQL Server 2005 SP2):
75% of visible target memory from 0 to 4GB + 10% of visible target memory from 4GB to 64GB + 25% of visible target memory > 64GB
Here’s the math:
If you have an ad hoc workload, you’ll often hit the max number of entries before you hit the space limit, particularly if you have the optimize for ad hoc workloads server option enabled, which stores the plan stub for an ad hoc query on initial execution, rather than the entire plan (the plan stub consumes less space, so this is an attempt to reduce bloat).
For pure ad hoc workloads, if you’re seeing SOS_CACHESTORE spinlock contention, you can use trace flag 174 to increase the number of entries to 160,001 per bucket (640,004 total), which is applicable for:
- SQL Server 2012 SP1 CU14+
- SQL Server 2012 SP2 CU5+
- SQL Server 2014 CU2+
- SQL Server 2014 SP1 CU1+
- SQL Server 2016+
Very rarely, I have seen instances where customers have used trace flag 8032 to increase the total size of the plan cache. This trace flag uses the plan cache limits for SQL Server 2005 RTM:
75% of visible target memory from 0 to 4GB + 50% of visible target memory from 4GB to 64GB + 25% of visible target memory > 64GB
The math is below, but note that this can significantly affect the memory available for the buffer pool and this is something I don’t typically recommend…use with caution.
If you’re seeing plan cache bloat due to an ad hoc workload, take note of the space consumed by your plan cache and consider enabling the optimize for ad hoc setting, and possibly enabling TF 174. Both of those are short term fixes – ideally you look to parameterize stable queries that are executed most frequently to reduce the number of entries in the plan cache.