Searching for table/index scans

Question

Does anyone have a query which searches through SQL2005/2008's plan cache identifying queries or stored procedures which have table/index scans within their execution plans?

Answer 1

That's taken a while to convince it to give me the answer but it is a minor alteration in the end. If you want the object name that is causing the scan then it cane be done, but there are problems.

There is one thing that is limiting the effectiveness of it. The scope of Object_Name / sys.Objects is to your database, so you are pulling all the plans from the cache for any database, but can only name those that are within the database you are currently using.

Object IDs are not guarenteed to be unique between databases so there is a chance the ID given matches to your current database, as well as another and you would get an incorrect name returned by it, so it is not perfect by any means.

Equally, if you see anything with an object ID > 0 but no name given means the plan was from an object not an adhoc query, but the information about that name is in a system view on a different database within the server.

On a server within a single DB it would be correct at least, but take the name it gives you as an indication, not gospel.

WITH XMLNAMESPACES
(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows,
PlanHandle,
QueryText,
QueryPlan,
CacheObjectType,
ObjectType,
ObjectID)
AS
(
    SELECT
    RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
    RelOp.op.value(N'@NodeId', N'int') AS OperationID,
    RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
    RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
    RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
    RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
    RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
    RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
    cp.plan_handle AS PlanHandle,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan,
    cp.cacheobjtype AS CacheObjectType,
    cp.objtype AS ObjectType,
    qp.objectid
    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
    CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)

SELECT 
PlanHandle,
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
QueryText,
CacheObjectType,
ObjectType,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows,
QueryPlan,
C.ObjectID,
Object_Name(C.ObjectID)
FROM CachedPlans C
Where 
(PhysicalOperator = 'Clustered Index Scan' 
  or 
PhysicalOperator = 'Table Scan' 
 or 
PhysicalOperator = 'Index Scan'
)