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.
I also saw this in my own environment along with a few other messages:
- 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.
- 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 '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:
The accepted answer states "Auto close on" is a likely culprit but doesn't state why and doesn't list other possibilities.
This is perfectly documented behaviour and the reasons you could be seeing that error message are listed on the Microsoft support site.
If you observe this error message on a somewhat regular basis it's likely because at that time every day the last connection to the database is closed and the database shuts down or it could be an agent job or scheduled job triggering one of the other conditions such as dropping a snapshot or running an sp_configure statement.
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.
It also happens if you change "maximum degree of paralelism and cost threshold for parallelism" settings in Instance.
