搜索表/索引扫描 [英] Searching for table/index scans

问题描述

是否有人通过 SQL2005/2008 的计划缓存搜索识别查询或在其执行计划中具有表/索引扫描的存储过程的查询?

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?

推荐答案

Pinal Dave 实际上写了一篇关于这个的帖子，对他的原始文章做了一些改动(几乎不需要任何改动！)你可以得到正确的答案.如果他有帐户，请相信他 :)

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/

他的查询是:

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'

最后你想要的是扫描类型(Clustered、Table 和 Index)

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

获取计划缓存的基本查询并不难，您可以手动创建 XQuery，但公平地说，Pinal 做了一个很棒的版本，所以我们不要重新发明它.

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.

这篇关于搜索表/索引扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！