Search Cache Plans Stored Procedure

Excellent article in SQL Server Central which inspects cached plans looking for poor performance. Full credit to Ian Stirk (Ian_Stirk@yahoo.com) for a great, easy to use and very useful routine.

Code is as follows :


CREATE PROC [dbo].[dba_SearchCachedPlans]
@StringToSearchFor VARCHAR(255)
AS
/*----------------------------------------------------------------------
Purpose: Inspects cached plans for a given string.
------------------------------------------------------------------------

Parameters: @StringToSearchFor - string to search for e.g. '%missingindexes%'.

Revision History:
03/06/2008 Ian_Stirk@yahoo.com Initial version

Example Usage:
1. exec dbo.dba_SearchCachedPlans '%missingindexes%'
2. exec dbo.dba_SearchCachedPlans '%columnswithnostatistics%'
3. exec dbo.dba_SearchCachedPlans '%tablescan%'
4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'

-----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor
ORDER BY cp.usecounts DESC
END