TSQLScripts.com: Script Details

TSQLScripts.com.

skip to the main content area of this page

Transact SQL scripts written by
DBAs and Developers for
DBAs and Developers.

To submit a comment about this script click here here or to submit your own script click here.

Get Performance Tips Directly From SQL Server
Description:
This script was written by Ian Stirk. You can read the full article here: http://www.sqlservercentral.com/articles/Performance+Tuning/66729/. This scripts makes use of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), so can be used by SQL Server 2005 or greater. The script allows you to inspect cached plans and extract information that can be useful in improving the performance of your SQL.
Submitted By: 2008DBA

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