dm_exec_cached_plans Bloat by Dave Ballantyne

Best practice is to use parametrized queries. It would seem that in many places SQLAgent does not follow this advice. Presently our live systems procedure cache has 550mb of irrelevant plans.

To get the total count...

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.%'


To get a summary of the queries...

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


Additionally the SQL executed seems to be peppered with (NOLOCK) 's and 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'. Again this is not best practice.


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'