May 26, 2011 at 7:56 am
Hi all,
Does anybody know how do i list of (cached or previously run)execution plans which include FULL-TABLE SCAN?
I take select * from sys.dm_exec_cached_plans, But there is not any identifier which displays whether it includes FULL-Table Scan.
Regards,
May 26, 2011 at 8:05 am
FULL table scan is Oracle terminology.
In SQL Server you just have "Table Scan", period.
-- Gianluca Sartori
May 26, 2011 at 8:11 am
You would need to look into the xml plan itself and look for table scan or clustered index scan operators.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2011 at 8:19 am
I Think, I found
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 PhysicalOperator = 'Table Scan'
:)))))
May 26, 2011 at 8:34 am
Two things:
That's only going to work for tables that don't have a clustered index. Table scans of a table with a clustered index show as clustered index scans
Do not run that against the plan cache on a busy production server. Not unless you're happy causing major problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2011 at 9:45 am
GilaMonster (5/26/2011)
Two things:That's only going to work for tables that don't have a clustered index. Table scans of a table with a clustered index show as clustered index scans
Do not run that against the plan cache on a busy production server. Not unless you're happy causing major problems.
Did you tell me to just do select into to move that data to another server and let it run over there?
May 26, 2011 at 9:51 am
Err, no I don't think so...
Grab the plans from cache, straight as they are, insert into a table. Get that data to another server (dev/test/monitoring server), do the xml querying there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2011 at 9:53 am
GilaMonster (5/26/2011)
Err, no I don't think so...Grab the plans from cache, straight as they are, insert into a table. Get that data to another server (dev/test/monitoring server), do the xml querying there.
Ya that's what I meant, but in much unclearer terms :hehe:.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply