Following on from my previous post in which I noticed that SQLServer itself was artificially bloating sys.dm_exec_cached_plans, the next obvious question is “How can i clear this rubbish out ?”.  On 2008 its simple enough, Microsoft have kindly enhanced DBCC FREEPROCCACHE to accept a plan_handle.  On 2005 things are a bit more tricky.  You could use DBCC FREEPROCCACHE , but that would clear everything out, certainly not a good thing to be happening in a live environment.  It has been blogged about before that sp_create_plan_guide can be used to purge a specific statement.  That works fine on single statement batches , but multi-statement  batches are not supported as easily.  The solution is in multi-statement batches to create a separate plan for each statement.  Here’s my rough-and-ready routine to clear down sys.dm_exec_cached_plans for msdb statements.

declare @Text nvarchar(max)
Declare @Plan_handle varbinary(64)
declare purgecur cursor for
 SELECT text,plan_handle
   FROM sys.dm_exec_cached_plans 
  CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
where Objtype = 'Adhoc'
   and text like '%msdb.%'
--   and plan_handle = 0x06000400E3854A1E40030F8E010000000000000000000000
open purgeCur
while(0=0) begin 
   Fetch Next from PurgeCur into @Text,@plan_handle
   
   if(@@Fetch_Status <> 0) break
   declare @CurCount integer
   Select @CurCount =0
   declare @StmtText nvarchar(max)
   declare purgestmt cursor
       for SELECT substring(text,(qs.statement_start_offset+2)/2,
                  (((case when statement_end_offset=-1 then 999998 else statement_end_offset end)-statement_start_offset)+2)/2)
             FROM sys.dm_exec_query_stats AS qs
                  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
            where qs.plan_handle = @Plan_handle
   open purgestmt
   
   while(0=0) begin 
       fetch next from purgestmt into @StmtText
       
       if(@@Fetch_status<>0) break
       select @CurCount =@Curcount+1
       begin try
       exec sp_create_plan_guide 
                   @name = N'PlanGuidePurge', 
                   @stmt = @StmtText,
                   @type = N'SQL', 
                   @module_or_batch = @Text, 
                   @params = NULL, 
                   @hints = N'OPTION (MaxDop 1)' 
       end try
       begin catch
       end catch           
       if exists(Select * from sys.plan_guides where name = N'PlanGuidePurge') begin    
          exec sp_control_plan_guide N'DROP', N'PlanGuidePurge'
       end
   end
   close purgestmt
   deallocate purgestmt
   if(@CurCount =0) begin 
       begin try
       exec sp_create_plan_guide 
                   @name = N'PlanGuidePurge', 
                   @stmt = @Text,
                   @type = N'SQL', 
                   @module_or_batch = @Text, 
                   @params = NULL, 
                   @hints = N'OPTION (MaxDop 1)' 
       end try
       begin catch
       end catch           
       if exists(Select * from sys.plan_guides where name = N'PlanGuidePurge') begin    
          exec sp_control_plan_guide N'DROP', N'PlanGuidePurge'
       end
    end
   
end
Close PurgeCur
Deallocate PurgeCur