SQL Server - Отсутствующие индексы - Что будет использовать индекс?

Ответ 1 · рейтинг 4 · KM.

вы можете попробовать что-то вроде этого запроса, в котором перечислены QueryText:

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, CachedPlans 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
         ,qp.query_plan AS QueryPlan
         ,st.TEXT AS QueryText
         ,cp.cacheobjtype AS CacheObjectType
         ,cp.objtype AS ObjectType
         ,cp.usecounts AS UseCounts
     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
        ,UseCounts
        ,CacheObjectType
        ,ObjectType
        ,EstimatedCost
        ,EstimatedIO
        ,EstimatedCPU
        ,EstimatedRows
        ,QueryText
    FROM CachedPlans
    WHERE CacheObjectType = N'Compiled Plan'

AND PhysicalOperator IN ('nothing will ever match this one!'
                        --,'Assert'
                        --,'Bitmap'
                        --,'Clustered Index Delete'
                        --,'Clustered Index Insert'
                        ,'Clustered Index Scan'
                        --,'Clustered Index Seek'
                        --,'Clustered Index Update'
                        --,'Compute Scalar'
                        --,'Concatenation'
                        --,'Constant Scan'
                        ,'Deleted Scan'
                        --,'Filter'
                        --,'Hash Match'
                        ,'Index Scan'
                        --,'Index Seek'
                        --,'Index Spool'
                        ,'Inserted Scan'
                        --,'Merge Join'
                        --,'Nested Loops'
                        --,'Parallelism'
                        ,'Parameter Table Scan'
                        --,'RID Lookup'
                        --,'Segment'
                        --,'Sequence Project'
                        --,'Sort'
                        --,'Stream Aggregate'
                        --,'Table Delete'
                        --,'Table Insert'
                        ,'Table Scan'
                        --,'Table Spool'
                        --,'Table Update'
                        --,'Table-valued function'
                        --,'Top'
                        )

просто добавьте ORDER BY к чему-то вроде комбинации UseCounts и EstimatedCost.

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') , CachedPlans as (
select
 query_plan,
 n.value('../../../@StatementText' ,'varchar(1000)') as [Statement],
 n.value('../../../@StatementSubTreeCost' ,'varchar(1000)') as [Cost],
 n.value('../../../@StatementEstRows' ,'varchar(1000)') as [Rows],
 n.value('@Impact' ,'float') as Impact,
 n.value('MissingIndex[1]/@Database' ,'varchar(128)') as [Database],
 n.value('MissingIndex[1]/@Table' ,'varchar(128)') as [TableName],
 (
  select dbo.concat(c.value('@Name' ,'varchar(128)'))
  from n.nodes('MissingIndex/ColumnGroup[@Usage="EQUALITY"][1]') as t(cg)
  cross apply cg.nodes('Column') as r(c)
 ) as equality_columns,
 (
  select dbo.concat(c.value('@Name' ,'varchar(128)'))
  from n.nodes('MissingIndex/ColumnGroup[@Usage="INEQUALITY"][1]') as t(cg)
  cross apply cg.nodes('Column') as r(c)
 ) as inequality_columns,
 (
  select dbo.concat(c.value('@Name' ,'varchar(128)'))
  from n.nodes('MissingIndex/ColumnGroup[@Usage="INCLUDE"][1]') as t(cg)
  cross apply cg.nodes('Column') as r(c)
 ) as include_columns
from (
 select query_plan
 from sys.dm_exec_cached_plans p
 outer apply sys.dm_exec_query_plan(p.plan_handle) tp
) as tab(query_plan)
cross apply query_plan.nodes('//MissingIndexGroup') as q(n)
)
select *
from CachedPlans