First published on MSDN on Jan 17, 2007
<br/><!--<br/> p.MsoNormal<br/> {mso-style-parent:"";<br/> margin-bottom:.0001pt;<br/> font-size:12.0pt;<br/> font-family:"Times New Roman";<br/> margin-left:0in; margin-right:0in; margin-top:0in}<br/> li.MsoNormal<br/> {mso-style-parent:"";<br/> margin-bottom:.0001pt;<br/> font-size:12.0pt;<br/> font-family:"Times New Roman";<br/> margin-left:0in; margin-right:0in; margin-top:0in}<br/>--><br/>
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:
-
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.
-
ALTER DATABASE [dbName] SET ONLINE
-
ALTER DATABASE [dbName] SET OFFLINE
-
ALTER DATABASE [dbName] SET READ_ONLY
-
ALTER DATABASE [dbName] SET READ_WRITE
-
ALTER DATABASE [dbName] MODIFY NAME = [SomeDB_Name]
-
ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 DEFAULT
-
ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_WRITE
-
ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_ONLY
-
ALTER DATABASE [dbName] COLLATE Collation_Name
-
DROP DATABASE [db_Snapshot_Name]
-
Restore database operation
-
Detach database operation
-
DBCC FREEPROCCACHE
-
DBCC FREESYSTEMCACHE
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.
