none
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.

    Question

  • Hi All

    Today 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. 
    /**************************************************/

    My question is why does flush of cache plans / SQL plans / bound trees happening when I am increasing max memory in sql server

    Tuesday, February 19, 2013 5:14 AM

Answers

  • Saurabh,

    Max server Memory is also 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 I dont see any improvement plans for cache flush because of memory changes  .  


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, February 20, 2013 9:12 AM

All replies

  • Yes it will.

    The following is the list of options that trigger the cachestore flush

    • cross db ownership chaining
    • index create memory (KB)
    • remote query timeout (s)
    • user options
    • max text repl size (B)
    • cost threshold for parallelism
    • max degree of parallelism
    • min memory per query (KB)
    • query wait (s)
    • min server memory (MB)
    • max server memory (MB)
    • query governor cost limit

    Refer http://support.microsoft.com/kb/917828


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, February 19, 2013 5:54 AM
  • Hi Karthick

    That's fine but i dont understand that how could increase sql server max memory cause  flush of cache plans / SQL plans / bound trees . And this is happening every time we increase max server memory even though sql server already have enough memory.  


    Thanks
    Saurabh Sinha
    http://saurabhsinhainblogs.blogspot.in/
    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Tuesday, February 19, 2013 6:45 AM
  • Saurabh,

    Max server Memory is also 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 I dont see any improvement plans for cache flush because of memory changes  .  


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, February 20, 2013 9:12 AM