DB Restore causes cache flush in SQL 2005

  • Article

Ok, I’m sure many people already know this , but I just found out the hard way.

When you perform a restore or certain reconfigure options, the entire plan cache is flushed – not only just the database. This might wreck havoc with shared environments or multi purpose servers.

The KB article is here
https://support.microsoft.com/kb/917828

One very interesting change between SQL 2005 and SQL 2008 is that DBCC FREEPROCCACHE now allows you to just remove a single plan from the cache. This could be extended to say remove all the plans for a single database. Here’s some sample code to do just that

CREATE PROC usp_freeproccache_db(@db_name sysname)
AS
BEGIN
    declare @i int
    declare @handle varbinary(64)       
    declare  proc_cursor cursor for
    SELECT plan_handle from sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)  AS st
    WHERE dbid =db_id(@db_name)
    set @i=0
    OPEN proc_cursor
    FETCH NEXT FROM proc_cursor into @handle
    WHILE @@FETCH_STATUS =0
    BEGIN
        DBCC FREEPROCCACHE (@handle) WITH NO_INFOMSGS
        FETCH NEXT FROM proc_cursor into @handle
        set @i=@i+1
    END
    CLOSE proc_cursor
    DEALLOCATE proc_cursor
    print convert (varchar(10),@i) + ' Plans removed from cache for ' + @db_name
END