September 10, 2010 1 Comment
Part of my regular database maintenance or tuning routine is to look for the queries with cached plans that are taking up the most space, and have only been used once. The query below is a handy query to track down the queries that may be in need of paramaterization.
SELECT TOP 100 refcounts,
size_in_bytes /1024 AS sizeinkb,
CROSS APPLY sys.Dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = ‘Compiled Plan’
AND objtype = ‘Adhoc’
AND usecounts = 1
AND TEXT NOT LIKE ‘FETCH API_CURSOR%’
ORDER BY size_in_bytes DESC
Once I find the big onetime use queries they get handed off to a developer to track down in the code and convert to use parameters.