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

Pinal Dave actually did a post about this and with a bit of alteration on his original article (barely any alteration required!) you can get the right answer. If he's got an account, credit him :)

http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/

His Query is:

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)
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
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
FROM CachedPlans
WHERE CacheObjectType = N'Compiled Plan'

And what you want on the end is specifically the scan types (Clustered, Table and Index)

and
(PhysicalOperator = 'Clustered Index Scan' or PhysicalOperator = 'Table Scan' 
or PhysicalOperator = 'Index Scan')

The basic query to get the plan cache isn't hard, and you can manually kruft the XQuery, but to be fair, Pinal did a great version of it so lets not reinvent it.

Answer 2

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'
)