There is quite a bit of sample code out there that talks about Single use plans and there side effects.
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]
- 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
The targeted SQL Server Version is v2005.