Wednesday, August 26, 2009 – The Rebirth and the Script of the Week

About two years ago I started, and initially the response was great.  Within two weeks I had received about 25 submissions.  This set me up for about 6 months of Script of the Week.  Unfortunately, the submissions came to a screeching halt and I really did not have the time to submit a script a week.  So I kind just let it fade. Since I have been blogging and becoming more active in the community, I have been receiving more and more emails about new scripts on the site. Therefore, I have decided to give the site “New Life”. I will be searching the Internet for scripts and attempting to write scripts that are worthy of the title, “Script of the Week”.  If I use a script that I did not write, I will give the author full credit.  So if any of you have some scripts that you would like to share with our SQL Server Community feel free to email it to or register at and submit it yourself. 


Once a week I will be posting “The Script of the Week” here.  If you are registered with you will receive the script via newsletter from the site.  This weeks script is written by Ian Stirk.  The script allows you to inspect cached plans and extract information that can be used in improving performance of your SQL Queries. 

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

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

Revision History:
03/06/2008 Initial version

-- Do not lock anything, and do not get held up by any locks.
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

Example Usage of the Stored Procedure:
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%'


If you want more details about the query go to:  If you want to see more scripts like Ian’s please visit

Talk to you soon

Patrick LeBlanc. Founder and the SQLLunch

SQL Down South

No comments:

Post a Comment