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. |
| … | |
| Fix/PastRel | |
| 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.
- 253
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.
- 2861
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.
- 2880
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).
- 2881
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?