Plan Caching

Most of the flags affect the size of the plan cache, or how certain types of plans (e.g. zero-cost, ad-hoc) are cached. This is a very small category and may be combined with another at a later time.

Short Descriptions

Flag Short Description
See Also These flags significantly affect the retention of cached plans.
205 Reports to error log when recompile occurs due to auto-update of stats.
2389 Enables optimizer tracking of ascending keys and subsequent recompilation behavior.
2390 Similar to 2390 and the ascending key problem.
2453 Applies recompile thresholds for temp tables to table variables
4139 Enables “quick stats” histogram amendments even if key col isn’t branded ascending.
* 2371 Lowers relative threshold for stats-based recompilation as table gets larger
Func Toggles  
174 Increases plan cache bucket count from 40k to 160k
253 (May) prevent ad-hoc query plans from being cached.
2861 Instructs SQL Server to keep zero cost plans in cache
8032 Reverts the cache limit parameters to the SQL Server 2005 RTM settings.
2880 Enables an upper bound for the plan cache in SQL 2000 32-bit.
2881 Turns off an upper bound on the plan cache in SQL 2000 64-bit

Functionality Toggles

174 Doc2014

BOL 2014: “Increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems.” Can relieve SOS_CACHESTORE spinlock contention though at the cost of increasing the amount of memory allowed for the plan cache.



SSC repository: “Prevents ad-hoc query plans to stay in cache.” Even though many “three-digit” query plan numbers don’t seem to be relevant any more, the first SO hit indicates that this flag still changes functionality in the manner described by the SSC definition. As a side note, Martin Smith’s comment in the second Stack Overflow hit about DAC queries never being cached is interesting.

StackOverflow_1 | DBAse_1


KB: “Instructs SQL Server to keep zero cost plans in cache, which SQL Server would typically not cache (such as simple ad-hoc queries, set statements, commit transaction and others).”

325607 | SolarWindsDPA | SQLMag_1 | DBAse_2

8032 Doc2008R2

BOL 2014: “Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger. Use this setting when frequently reused cache entries do not fit into the cache and when the optimize for ad hoc workloads Server Configuration Option has failed to resolve the problem with plan cache. WARNING: Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.”

Banerjee: Can’t be used as a startup flag; @sql_handle tweet indicates it can ONLY be used as a startup flag in SQL 2014 (and I would guess in 2012 as well, since that’s when the SQLOS mem rearchitecture occurred). Another @sql_handle tweet indicates it also increases size of log pool cache.

Fixes and Past Relevance

These flags either are old and irrelevant for modern builds, appear only in CTPs, or enable a fix in a CU but are baselined in a later service pack or release.


Enables an upper bound for the plan cache in SQL 2000 32-bit. (The upper bound is by default disabled in 32-bit SQL 2000 and enabled in 64-bit SQL 2000).



Turns off an an upper bound on how large the plan cache can get. (Upper bound introduced in 64-bit SQL 2000 to solve a prob with the plan cache and adhoc sql). Does the flag still do anything?