Big One Time Use Queries – Revisited

Just the same query that I posted months ago, but this time it is wrapped in a stored proc.  I use this one often enough that it is easier to just call the stored proc.

Download the FindOneTimeUseQueries source code.

TSQL example:

CREATE PROCEDURE [dbo].[Findonetimeusequeries]
AS
BEGIN
SET nocount ON;

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

END

 

This posting replaces an older posting from September 10, 2010.

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!