10.0 Plan Cache Flush


 


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.

Comments (1)

  1. shus says:

    Plan Cache Flushing – We recommend doing the following instead of attaching/detaching the DBs of those partners:

    1) Redirect or bring down the front-end server (web server/services or application server) for those partners

    2) Putting the DB in RESTRICTED_USER mode

    3) If you must bring the DB on/off, you should put the databases that will likely to be brought on/off line in a separate DB instance from the DB instances of those that will be always online.

    SQL 2000 allows you to detach a DB without flushing the whole proc cache, but it does it incorrectly under certain situations. SQL 2005 fixed this problem by flushing the whole plan cache. Currently, due to resource constraints, we do not have plans to do DB specific plan cache flushing. We certainly will keep your feedback in mind going forward.

    For the parameter sniffing problem – If you use one of the suggestions above, the parameter sniffing problem will be reduced. The other things you can do are:

    1) Use plan guide for those queries

    2) Use “OPTIMIZE FOR” query hint

    3) Use “with recompile” (limited for stored procs or use plan guide)

    I would love to hear more detailed cases about the parameter sniffing problems.

    -Shus