Find single-use, ad-hoc, and prepared queries

Ever wondered what's lurking in your SQL Server's plan cache? This handy query helps you find single-use ad-hoc and prepared statements that might be cluttering up your memory. Identifying these can be a great first step in optimizing your database performance!

SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name]
 ,t.[text] AS [Query Text]
 ,cp.objtype AS [Object Type]
 ,cp.cacheobjtype AS [Cache Object Type]
 ,cp.size_in_bytes / 1024 AS [Plan Size in KB]
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE cp.cacheobjtype = N'Compiled Plan'
 AND cp.objtype IN (N'Adhoc', N'Prepared')
 AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC
 ,DB_NAME(t.[dbid])
OPTION (RECOMPILE);

This query leverages two powerful Dynamic Management Views (DMVs) in SQL Server to gather its insights:

• sys.dm_exec_cached_plans: This DMV provides information about query plans in the cache.
• sys.dm_exec_sql_text: Use this to retrieve the actual text of a SQL batch or individual statement.