SQL and the like

Cleaning up sys.dm_exec_cached_plans

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

Microsoft – Follow best practices – Part 2

In addition to my previous post, another best practice is to not use NOLOCK and READ UNCOMMITTED transaction isolation level.

Here’s an excerpt from a profiler trace

BEGIN TRAN UpdateMediaTables

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @BackupSetId int, @MediaSetId int, @LogDevName varchar(512)

SELECT @MediaSetId = media_set_id
FROM msdb..backupmediafamily AS bmf WITH (NOLOCK)
WHERE substring(bmf.physical_device_name,5,36) = '80A2E6DE-3E95-4645-B476-09E37306FF8C'

SELECT @BackupSetId = backup_set_id
FROM msdb..backupset WITH (NOLOCK)
WHERE media_set_id = @MediaSetId

So, not only do we have no consistency, but also a non–SARGable lookup.

I’ve updated my previous connect item to reflect this also.

Microsoft – Follow best practices!

Best practice is to use parametrized queries to enable plan reuse.  Will someone please tell Microsoft this. 

Presently dm_exec_cached_plans on our live server has been bloated by 550mb of adhoc queries by SqlAgent (possibly a few other services)

Here’s how im calculating the total bloat value

SELECT sum(size_in_bytes)
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
where Objtype = 'Adhoc'
  and text like '%msdb.%'

Which can be broken down to a query by query basis of

SELECT sum(size_in_bytes),count(*),substring(text,1,100)
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
where Objtype = 'Adhoc'
  and text like '%msdb.%'
group by substring(text,1,100) order by 1 desc

Connect item here if you feel like voting.