In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc freeproccache” or “dbcc freesystemcache” will clear the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and cause a sudden, temporary excessive CPU usage and decrease in query performance. To help identify database operations flushing the plan cache as the cause of the sudden temporary decrease in query performance, SP2 includes an informational message in the ERRORLOG: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.
The following database operations will flush the plan cache:
In SQL Server 2005 RTM and SP1, executing DBCC CHECKDB clears the plan cache for the instance of SQL Server. In SP2, executing DBCC CHECKDB does not clear the plan cache.