Memory is cheap and often easy to expand in a modern Windows server. But…
The KB-article hasn’t been updated since SQL Server 2005 and since then there is new settings added as well as changes to the behavior on the previous settings.
* Official KB-article:
You may experience a decrease in query performance after you perform certain database maintenance operations or regular transaction operations in SQL Server 2005
Gladly there is less configuration changes that affects the whole SQL instance now, for example in SQL Server 2005 by just restoring one single database caused the the cache to be flushed for the whole SQL instance.
But there are a couple of changes that still affects the SQL instance and a few that I find remarkable for an enterprise database engine. Why should the cache be flushed by enabling CLR integration to be able to add new features to a database or increasing the maximum memory for SQL Server if there is a system that isn’t under heavy load.
The behavior is logged to the SQL Server Errorlog:
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Some of the configurations are set by using SQL Server Management Studio or more granular by using the sp_configure system stored procedure. And these settings are stored in the sys.configurations catalog view.
The whole procedure cache is cleared if one of the following server options is changed by the Reconfigure statement:
• cost threshold for parallelism
• cross db ownership chaining
• index create memory (KB)
• max degree of parallelism
• min memory per query (KB)
• min server memory (MB)
• max server memory (MB)
• max text repl size (B)
• query governor cost limit
• query wait (s)
• remote query timeout (s)
• user options
Also for these server options the cache is cleared (not included in the original KB-article since they are features added after SQL Server 2005):
• access check cache bucket count
• access check cache quota
• clr enabled
• max worker threads
• set working set size
• user connections
In SQL Server 2014 (not in SQL 2012) setting these server options will clear the cache:
• affinity I/O mask (Also requiring a restart)
• affinity64 I/O mask
• affinity mask
• affinity64 mask (Also requiring a restart)
• filestream access level (Note that requiring a SQL restart for setting to off)
• locks (Also requiring a restart)
Note: There is a change in behavior because in SQL Server 2012 the procedure cache will not be cleared if the actual value doesn’t change but in SQL 2014 the procedure cache will be flushed even if the actual value doesn’t change.
So be aware when there is configuration scripts that needs to be run to setup a new application database because that even if the setting isn’t changed and will remain as the old value then it will anyway trigger a flushed cache in SQL 2014.
SQL Server 2012 Standard x64 Edition Service Pack 2 (build 11.0.5058) showing that Profiler indicates; SP:CacheRemove – ”Entire Procedure Cache Flushed”.
SQL Server 2014 Standard x64 Edition Cumulative update package 6 (build 12.0.2480) showing that Profiler indicates; SP:CacheRemove – ”Entire Procedure Cache Flushed”.
* A nice script by Thomas LaRock to list the settings of values that differs from default
* MSDN-article: Server Configuration Options (SQL Server)