Configuring SQL Server for Optimization - Microsoft SQL Server 2012 Performance Tuning - page 400

sys.dm_exec_sql_text(plan_handle) AS ST
WHERE
CP.usecounts > 0
GO

Here is the result I received on my development server; you will get a different result
in your environment:

2. Now, clear the cache and buffer:

--don't execute these two commands on production server
--this is just to prove the case given in this recipe...
--this should run on testing or development servers only
DBCC FREEPROCCACHE
GO

3. If you want to check whether our saved plan is cleaned up so far, execute the query

we ran in step 1, again:

SELECT
CP.usecounts AS CountOfQueryExecution
,CP.cacheobjtype AS CacheObjectType
,CP.objtype AS ObjectType
,ST.text AS QueryText
FROM
sys.dm_exec_cached_plans AS CP
CROSS APPLY