Big One Time Use Queries

Download PDF

There is a newer version of this posting, click here.

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.

TSQL example:

SELECT TOP 100 refcounts,
size_in_bytes /1024 AS sizeinkb,
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.Dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = ‘Compiled Plan’
AND objtype = ‘Adhoc’
AND usecounts = 1
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.

Tagged with: ,

Leave a Reply

Your email address will not be published. Required fields are marked *


Time limit is exhausted. Please reload CAPTCHA.