Big One Time Use Queries

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,
usecounts,
size_in_bytes /1024 AS sizeinkb,
TEXT
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
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.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!