You might have seen the below messages in SQL Server error log at some point of time –

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.

In the event you see this message,your next course of action should be checking if any of the databases are having property ‘AutoClose’  is set to True.

There are other conditions too which triggers this message,however you dont want your databases to have AutoClose set as True for production environment at all.

We can query the sys.databases view to get this details pretty easy.

--Query the sys_databases view for autoclose details
SELECT name 
FROM   sys.databases 
WHERE  is_auto_close_on <> 0

Obviously this question comes to your mind – What is the relation between Auto Close being turned ON and this cachestore flush message ?

The answer to that question is pretty much explained well in MSDN.

 When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance.

Lets do a quick demo to demonstrate this behaviour.

Demo

1. Using Adventureworks database running on SQL Server 2008 R2

2. I will now do a simple SELECT * from one of the tables.I will run the statement twice just to increase the execution count.

--Select query to pull the records
SELECT * 
FROM   production.WorkOrder

3. I will now query the plan cache to retrive the details of SELECT statement ran above

--Query plan cache for finding the SELECT Statement
SELECT CP.objtype                          AS ObjectType, 
       Object_name(SXT.objectid, SXT.dbid) AS ObjectName, 
       CP.usecounts                        AS ExecutionCount, 
       SXT.TEXT                            AS QueryText, 
       CP.plan_handle 
FROM   sys.dm_exec_cached_plans AS CP 
       CROSS apply sys.Dm_exec_sql_text(CP.plan_handle) AS SXT 
WHERE  CP.usecounts = 2

You can clearly see the Query Text(Select statement) and the plan_handle in the cache.Note down the plan_handle as it will be required later on

0x06000800557BB714B8E0F30A000000000000000000000000

4. Now we will set Auto Close property for the database Adventureworks as True.

--Set Autoclose to ON
USE [master]  go  ALTER DATABASE [Adventureworks]  SET auto_close ON WITH no_wait  go 

5. Next we will again query the plan cache to confirm that the details have been flushed as the Auto Close property was turned ON and the database was closed.

--Query plan cache for finding the SELECT Statement
SELECT CP.objtype                          AS ObjectType, 
       Object_name(SXT.objectid, SXT.dbid) AS ObjectName, 
       CP.usecounts                        AS ExecutionCount, 
       SXT.TEXT                            AS QueryText, 
       CP.plan_handle 
FROM   sys.dm_exec_cached_plans AS CP 
       CROSS apply sys.Dm_exec_sql_text(CP.plan_handle) AS SXT 
WHERE plan_handle = 0x06000800557BB714B8E0F30A000000000000000000000000

(0 row(s) affected)

Conclusion

Next time when you see this error make sure that Auto Close property is definitely cross checked and ensure that its not turned ON.