Tuesday, February 14, 2012

SQL Server has encountered 5 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 5 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 5 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

If you see messages like the above in your SQL Server error logs then you know you have a problem.
This means that the cache is being flushed and all the plans and data will have to cached in again. This is a painful process and will result in an immediate but a temporary hit in performance.

Following are some of the reasons that can cause this

1) The database is set to AUTOCLOSE = ON, an operation that initiates an automatic database  shutdown or disconnects from the database, clears the plan cache for the instance of SQL Server.
2) Some of the ALTER DATABASE commands.
3) DROP DATABASE [dbSnapshot]
4) Restore database operation
5) Detach database operation
6) DBCC FREEPROCCACHE
7) DBCC FREESYSTEMCACHE

In most of the cases the commands are not likely to be executed regularly so most of them are not much of a concern.
The most frequent reason that I have seen is the AUTOCLOSE property.

If you see messages like the following one in the error logs, then it is AUTOCLOSE that is causing the issue.

Starting up database 'yourDBname'

Check if you have any database that has AUTOCLOSE set to true. You can see that in the database propertise 'OPTIONS' tab. Or run the following query to identify any database with that set to TRUE.

select name, is_auto_close_on from sys.databases where is_auto_close_on=1

If you do find any database with that property to to TRUE then I would suggest that you turn it off by using the following commands.

USE [master]
GO
ALTER DATABASE [yourDBname] SET AUTO_CLOSE OFF WITH NO_WAIT
GO
ALTER DATABASE [yourDBname] SET AUTO_CLOSE OFF
GO

This should fix the problem if the AUTOCLOSE property is indeed the issue. Else you will have to start a trace and capture any event where it says 'Plan Cache Flush'. I am still investigating the event Id for this so please leave a comment if you know that.




No comments:

Post a Comment