TSQLScripts.com – The Rebirth and the Script of the Week

About two years ago I started www.TSQLScripts.com, 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 pleblanc@tsqlscripts.com or register at TSQLScripts.com and submit it yourself. 

 

Once a week I will be posting “The Script of the Week” here.  If you are registered with TSQLScripts.com 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) 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 -----------------------------------------------------------------------*/ 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

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:http://www.sqlservercentral.com/articles/Performance+Tuning/66729/.  If you want to see more scripts like Ian’s please visit www.tsqlscripts.com

Talk to you soon

Patrick LeBlanc. Founder TSQLScripts.com and the SQLLunch

SQL Down South