What events will cause a flushing of plan in cache?

Published 14 May 07 05:17 AM | SQL Master 

We already know that using DBCC FREEPROCCACHE will have the affect of flushing all the cache with immediate affect and also DBCC FREESYSTEMCACHE (new in SQL 2005) has similar affect but will flush the plans asynchronously that are marked as unused anymore. Also you would know that when a database is RESTORed or DETACHed then it will have same affect.

How about other events in the SQL Server will have similar affect in addition to above DBCC statements execution. The following database operations will flush the plan cache:

  • If AUTOCLOSE option is set to ON on a database, being the reason operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server.

ALTER database statement will cause this event too and following set of actions in this case:

    • 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 = <DB_Name>
    • ALTER DATABASE [dbName] MODIFY FILEGROUP
    • ALTER DATABASE [dbName] MODIFY FILEGROUP
    • ALTER DATABASE [dbName] MODIFY FILEGROUP <FileGroupName> READ_ONLY
    • ALTER DATABASE [dbName] COLLATE Collation_Name

 

  • DROP DATABASE [db_Snapshot_Name] as this will clears every content related to that particular database which is by default.
    •  

Further to the subject I would like to refer similar blog post that will help you to understand the plan cache flushing concept in SQL Server.

 

 

Comments

# SSQA - SqlServer-QA.net said on May 14, 2007 5:44 AM:

We already know that using DBCC FREEPROCCACHE will have the affect of flushing all the cache with immediate

# jackpatel said on May 19, 2007 3:25 PM:

Helpful information above, but how to avoid flushing of cache in case of above statements. Say I have a development servers where i dont want to flush cache with these statements?

# SQL Master said on May 21, 2007 12:58 AM:

You cannot avoid that as it is a default configuration behaviour by SQL engine. In this case better to avoid these statements when you have a resource intensive query to execute.

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.