5

On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue?

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.

8

I also saw this in my own environment along with a few other messages:

  1. 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.
  2. 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.
  3. 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.

It turns out there were multiple databases with the AutoClose setting turned on.

You can check your own database with this command:

SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')

Once we turned the setting off, the messages went away and performance improved. For further reading as to why auto close is a bad idea:

2

This can also occur due to an sp_configure change for 'remote query timeout (s)'. I found this in my log directly before the cachestore flush message.

Configuration option 'remote query timeout (s)' changed from 1000 to 900. Run the RECONFIGURE statement to install.

2

It also happens if you change "maximum degree of paralelism and cost threshold for parallelism" settings in Instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.