SQL Server - Missing Indexes - What would an Index Use?

Question

I am using SQL Server 2008 and we are using DMV to search for missing indexes. However, before I create a new index, I try to figure out which proc / query this index wants. I want to get the most information in order to get an informed decision on my indicators. Sometimes the indexes that SQL Server wants do not make sense to me. Does anyone know how I can understand what he needs?

Answer 1

Here is what finally worked:

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

Answer 2

you can try something like this query that lists a 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.

Answer 3

/ .

, , - .

, proc/tsql . , .