SQL and the like

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.