Script: Query Full Table Scans via the Search Cached Plans’ DMV

SQL Server Dynamic Management Views (DMVs) are a powerful feature set introduced with SQL 2005 that allows you to pull back the curtain on SQL Server and easily review some of the inner workings of your database server (such as performance issues etc) without first having to collect a full SQL trace then interrogating that data through utilities such as Tuning Advisor.

This script below creates a simple stored procedure that allows you (as SQL sys-admins) to quickly output a report on Missing Indexes, Columns with No Statistics or Full Table Scans without having to enter in a full DMV query (which can be quite complicated).

CREATE PROC [dbo].[dba_SearchCachedPlans]
@StringToSearchFor VARCHAR(255)
AS

/*———————————————————————-
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

To invoke the stored procedure, simply enter the following and specify the search you’re after; MissingIndexes, ColumnsWithNoStatistics and TableScan!

exec dbo.dba_SearchCachedPlans ‘%%’