SQL Server – Cached Plans – The single used ones

Introduction

There is quite a bit of sample code out there that talks about Single use plans and there side effects.

 

Code

Here is one more sample code taken from one posted by Thomas Larock on the SolarWind’s website.

The post is titled “Improve SQL Server Performance by Looking at Plan Cache (Part 1)” and it is available here 


/*
	Author :- Thomas Larock
	Title  :- Single Used Plans
	Web Link :- http://logicalread.solarwinds.com/sql-server-minimize-single-use-plans-tl01/#.V9GzBPnytD8
*/	
SELECT 

		  [database]
			=  case
				   when ( tblDEPA_DBID.[value] = 32767 ) then 'Resource DB'
				   else db_name(cast(tblDEPA_DBID.[value] as smallint))
			   end

        ,  [CacheType]
			=  [objtype]

        , [Total Plans]
			=count_big(*)


        , [Total MBs]
			= sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 

        , [Avg Use Count]
			= avg(usecounts)

        , [Total MBs - USE Count 1]
			= sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024

        , [Total Plans - USE Count 1]
			= sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) 


FROM sys.dm_exec_cached_plans tblDECP

CROSS APPLY sys.dm_exec_plan_attributes(tblDECP.plan_handle) AS tblDEPA_DBID

where tblDEPA_DBID.[attribute] IN ('dbid')

GROUP BY 
				tblDECP.objtype
			, 	tblDEPA_DBID.[attribute]
			, 	tblDEPA_DBID.[value]


ORDER BY 
				[Total MBs - USE Count 1] desc
			,   [database]
			, 	[CacheType]


 

Output

singleusedplans-20160908-1155am

 

Explanation

  1. In our case most of them in terms of memory consumption is slanted towards MSDB
    • Interesting enough they are “Prepared” and not “Ad Hoc” variants, as well

 

Environment

The targeted SQL Server Version is v2005.

One thought on “SQL Server – Cached Plans – The single used ones

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s