Why Does Flush of Cache Plans / SQL Plans / Bound Trees Happening When I am Increasing Max Memory in SQL Server


Last year I was working on impact of change in max server memory so I have changed max server memory from 100 to 16 MB and I get below in error log

/******************************************************/
Configuration option 'max server memory (MB)' changed from 100 to 16. Run the RECONFIGURE statement to install.
FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
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.

/******************************************************/

But when I changed from 16 to 100 or to 1000 MB i get below again

/******************************************************/
Configuration option 'max server memory (MB)' changed from 16 to 100. Run the RECONFIGURE statement to install.
FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
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.
/**************************************************/

This was unexpected, why does flush of cache plans / SQL plans / bound trees happening when I am increasing max memory in SQL server

So bit goggling gives me couple of details.

The following is the list of options that generates  the cachestore flush output in error log. Here nothing says if it actually triggers  cachestore flush or its just some destructor which have print statement to output "cachestore flush" in error log. For Max server Memory it actually triggers "cachestore flush " , the reason to flush is share as clarification at end of post

  1. cross db ownership chaining
  2. index create memory (KB)
  3. remote query timeout (s)
  4. user options
  5. max text repl size (B)
  6. cost threshold for parallelism
  7. max degree of parallelism
  8. min memory per query (KB)
  9. query wait (s)
  10. min server memory (MB)
  11. max server memory (MB)
  12. query governor cost limit


Clarification from Kathick: Max server Memory is  a factor which will impact the generation of execution plan by the optimizer, So plan generated when you have X GB of max server memory may not be the right plan to use when you have Y GB of RAM. So ideally the plans have to be flushed when we change the max server memory. There was lot of improvements in plan cache flush in 2008 compared with 2005 but we didn't identify improvement in plans for cache flush because of memory changes.

1 comment: