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

Query Full Table Scans and Missing Indexes Using Cached Plan DMVs

SQL Server's plan cache holds a goldmine of diagnostic information, and you can query it directly using DMVs to surface full table scans, missing indexes, and columns with no statistics - without running a trace or waiting for a performance incident to escalate. The stored procedure below wraps that query into a reusable tool any DBA or sysadmin can run in under a minute.

If you're managing SQL Server environments and haven't yet built this kind of diagnostic tooling into your workflow, you're doing performance troubleshooting the hard way.

Why the Plan Cache Matters for Performance Diagnostics

When SQL Server executes a query, it compiles an execution plan and caches it in memory. That cached plan contains far more information than most people realise. Embedded in the XML of each plan are warnings and annotations that SQL Server itself generates, including flags for missing indexes, full table scans, and columns with no statistics.

The key DMV here is sys.dm_exec_cached_plans, introduced in SQL Server 2005. Combined with sys.dm_exec_sql_text and sys.dm_exec_query_plan, you can inspect the actual execution plans sitting in memory right now, without needing to capture a trace or run the Database Engine Tuning Advisor.

This matters in production environments because:

• Full table scans on large tables are one of the most common causes of blocking and slow query performance
• Missing index warnings in cached plans point directly to queries SQL Server believes would benefit from an index
• Columns with no statistics can lead to poor cardinality estimates, causing the optimiser to choose inefficient execution plans

The plan cache approach gives you a snapshot of what's actually running on your server, not a synthetic workload or a trace from last Tuesday.

What Does the Script Actually Do?

The stored procedure below accepts a search string and runs it against the XML of every cached query plan on the instance. It returns the top 20 results ordered by plan reuse count, so the most frequently executed problematic queries surface first.

The three most useful search patterns are:

1. '%<MissingIndexes>%' - finds cached plans where SQL Server has identified a missing index
2. '%<ColumnsWithNoStatistics>%' - finds plans where statistics are absent on referenced columns
3. '%<TableScan>%' - finds plans containing full table scan operations

The usecounts column tells you how many times each plan has been executed from cache. A full table scan that's been executed 50,000 times is a very different priority to one that's run twice. That ordering is what makes this script operationally useful rather than just academically interesting.

The Stored Procedure

Create this procedure in your DBA utility database or directly in the target database. It uses READ UNCOMMITTED isolation to avoid adding any lock pressure during the diagnostic query itself.

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

How to Use It

Once the procedure is created, invoke it with one of the search patterns depending on what you're investigating:

-- Find queries with missing index recommendations in their cached plans
EXEC dbo.dba_SearchCachedPlans '%<MissingIndexes>%'

-- Find queries affected by columns with no statistics
EXEC dbo.dba_SearchCachedPlans '%<ColumnsWithNoStatistics>%'

-- Find queries performing full table scans
EXEC dbo.dba_SearchCachedPlans '%<TableScan>%'

The query_plan column returns clickable XML in SQL Server Management Studio. Click it and SSMS will render the graphical execution plan, making it straightforward to see exactly where the scan or missing index sits within the query's execution.

What to Watch Out For

A few practical caveats worth knowing before you act on the results.

The plan cache is volatile. SQL Server evicts plans under memory pressure, after statistics updates, after index changes, and when the server restarts. If you're investigating an incident that happened hours ago and the server has been under load since, the relevant plan may no longer be cached. Run this script as close to the performance event as possible.

Table scans aren't always wrong. A table scan on a 500-row lookup table is perfectly reasonable. The scan on a 50-million-row transaction table is the one that needs attention. Always check the DatabaseName and the actual SQL text before raising an alarm.

Missing index recommendations are suggestions, not instructions. SQL Server's missing index DMVs are known to be overly aggressive. They don't account for the overhead of index maintenance, storage costs, or the impact on write performance. Treat each recommendation as a starting point for analysis, not a to-do list to execute blindly. Microsoft's own documentation notes that missing index suggestions should be evaluated carefully before implementation.

The CAST to NVARCHAR(MAX) has a cost. On instances with large plan caches (common on busy OLTP systems), this query can take a few seconds to run. It's not a query to schedule every minute. Use it on demand during investigations or as part of a scheduled weekly diagnostic report.

Ad-hoc queries may not appear. If your instance has "Optimise for Ad hoc Workloads" enabled (which it should on most production servers), single-execution ad-hoc queries store only a plan stub rather than the full plan. This script won't surface those. That's a reasonable trade-off given the memory savings that setting provides.

Extending the Script

The pattern matching approach is flexible. You can combine search terms to narrow results further. For example, to find stored procedures specifically that contain missing index warnings:

EXEC dbo.dba_SearchCachedPlans '%Proc%<MissingIndexes>%'

You can also adjust the TOP 20 to return more results, or add filters on cp.objtype to restrict results to stored procedures, ad-hoc queries, or prepared statements.

For ongoing monitoring, consider wrapping results from this procedure into a logging table on a weekly schedule. Tracking which queries consistently appear with table scan warnings over time gives you a prioritised index improvement backlog rather than a one-off snapshot.

Key Takeaways

• The dba_SearchCachedPlans stored procedure lets you query SQL Server's plan cache directly to find full table scans, missing indexes, and statistics gaps using simple pattern matching against cached plan XML.
• Results are ordered by usecounts, so the highest-impact queries surface first - a full table scan executed 100,000 times is far more urgent than one executed twice.
• The plan cache is volatile. Run this script during or immediately after a performance event for the most relevant results.
• Missing index warnings from the plan cache are starting points for analysis, not automatic implementation candidates. Evaluate each one against write overhead and storage impact.
• This approach requires no trace, no Tuning Advisor session, and no elevated permissions beyond VIEW SERVER STATE, making it practical for routine use in production environments.

If you'd like a more comprehensive view of your SQL Server's performance posture, DBA Services offers SQL Server health checks that cover execution plan analysis, index fragmentation, statistics health, and wait statistics across your entire environment. It's the fastest way to get an expert second opinion on what's actually hurting your database performance.