Wednesday, 20 February 2013

Recompilation Of Execution Plan after plan cache flushing


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.

The following operations cause the plan cache flushing



  • 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

No comments:

Post a Comment