Find queries performing full scans

I have a monitoring system that is telling me that the Full Scans /sec on one of my servers is rather high. I am trying to determine which queries may be causing the issue. Is there a simple/easy way to determine which queries are performing full scans on the server?

This query will identify Table and Index Scans where no seek predicate is present in the query plan. Keep in mind this query is pretty computationally and I/O heavy, so you should probably not run this on a schedule in production.

Also, keep in mind, since this looks at the plan cache, there are a variety of circumstances where queries may be performing scans that are not reported, including plans that have been evicted from the cache, single-use plan stubs when ad-hoc query plans are enabled, queries with OPTION (RECOMPILE), zero-cost plans, etc.

By design, I've filtered out #temp tables and @table variables. Depending on your requirements, it might be good to eliminate those filters.

Also, if you have plans with over 128 levels of nesting, the xquery processor will report an error.

The first column of the results shows the number of times the plan has been used - this number is reset if the plan is evicted, or if the server restarts, or if the plan cache is cleared. The 2nd column is the entire query plan - if you click the value in the results pane of SQL Server Management Studio, you'll see the nice graphical plan pop up. The TableScans and IndexScans columns contain details about each particular table or index that was scanned, including the columns pertinent for the query.

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS qp)
SELECT decp.usecounts
    , QueryPlan = deqp.query_plan
    , TableScans = CONVERT(xml, 
        (
        SELECT ts.x.query('..') 
        FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:TableScan/qp:Object') ts(x) 
        WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
            AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
            AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
        FOR XML PATH (''), ROOT ('xml'))
        )
    , IndexScans = CONVERT(xml, 
        (
        SELECT ts.x.query('..') 
        FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:IndexScan/qp:Object') ts(x) 
        WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
            AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
            AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
        FOR XML PATH (''), ROOT ('xml'))
        )
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) deqp
WHERE (
    deqp.query_plan.exist('/qp:ShowPlanXML//qp:TableScan') = 1
        AND EXISTS (
            SELECT 1
            FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:TableScan/qp:Object') ts(x) 
            WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
                AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
                AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
            )
    )
    OR (
    deqp.query_plan.exist('/qp:ShowPlanXML//qp:IndexScan') = 1
        AND EXISTS (
            SELECT 1
            FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:IndexScan/qp:Object') ts(x) 
            WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
                AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
                AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
            )
    )
ORDER BY decp.usecounts DESC;

Another caveat emptor for the above query; it filters #temp tables by looking for table names that have a # anywhere in the name. So, [this#is#a#dumb#name] will be excluded. As would [hell@].

Also, the wonderful sp_BlitzCache, by the Brent Ozar team, does a great job looking for scans. FYI, I'm not affiliated with them, I just think they do great work.

StackExchange Database Administrators Q#89211, answer score: 3