What is the meaning of the "Missing Index Impact %" in a SQL Server 2008 execution plan?

Question

I was just examining an estimated execution plan in SSMS. I noticed that a query had query cost of 99% (relative to the batch). I then examined the plan displayed below. That cost was almost entirely coming from a "Clustered Index Delete" on table A. However, the Missing Index recommendation is for Table B. And the Missing Index Impact is said to be 95%.

The query is a DELETE statement (obviously) which relies on a nested loops INNER JOIN with TableB. If nearly all the cost according to the plan is coming from the DELETE operation, why would the index suggestion be on Table B which -- even though it was a scan -- had a cost of only 0%? Is the impact of 95% an impact against the neglible cost of the scan (listed as on 0%) and not the overall cost of the query (said to be nearly ALL of the batch)?

Please explain IMPACT if possible. Here is the plan:

Answer 1

This is query 27 in the batch.

Probably the impact it is showing you actually belongs to an entirely different statement (1-26).

This seems to be a problem with the way that the impacts are displayed for estimated plans in SSMS.

The two batches below contain the same two statements with the order reversed. Notice in the first case it claims both statements would be helped equally with an impact of 99.38 and in the second 49.9818.

So it is showing the estimated impact for the first instance encountered of that missing index - Not the one that actually relates to the statement.

I don't see this issue in the actual execution plans and the correct impact is actually shown in the plan XML next to each statement even in the estimated plan.

I've added a Connect item report about this issue here. (Though possibly you have encountered another issue as 10% impact seems to be the cut off point for the missing index details being included in the plan and it is difficult to see how that would be possible for the same reasons as described in the question)

Example Data

CREATE TABLE T1   (      X INT,      Y CHAR(8000)   )  INSERT INTO T1             (X) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@spid) FROM   sys.all_objects o1,        sys.all_objects o2  

Batch 1

SELECT * FROM T1 WHERE X = -1  SELECT * FROM T1 WHERE X = -1 UNION ALL SELECT * FROM T1 

Batch 2

SELECT * FROM T1 WHERE X = -1 UNION ALL SELECT * FROM T1  SELECT * FROM T1 WHERE X = -1 

The XML for the first plan (heavily truncated) is below, showing that the correct information is in the plan itself.

<?xml version="1.0" encoding="utf-16"?> <ShowPlanXML>   <BatchSequence>     <Batch>       <Statements>         <StmtSimple StatementCompId="1">           <QueryPlan>             <MissingIndexes>               <MissingIndexGroup Impact="99.938">                 <MissingIndex Database="[tempdb]" Schema="[dbo]" Table="[T1]">                   <ColumnGroup Usage="EQUALITY">                     <Column Name="[X]" ColumnId="1" />                   </ColumnGroup>                 </MissingIndex>               </MissingIndexGroup>             </MissingIndexes>           </QueryPlan>         </StmtSimple>       </Statements>       <Statements>         <StmtSimple StatementCompId="2">           <QueryPlan>            <MissingIndexes>               <MissingIndexGroup Impact="49.9818">                 <MissingIndex Database="[tempdb]" Schema="[dbo]" Table="[T1]">                   <ColumnGroup Usage="EQUALITY">                     <Column Name="[X]" ColumnId="1" />                   </ColumnGroup>                 </MissingIndex>               </MissingIndexGroup>             </MissingIndexes>           </QueryPlan>         </StmtSimple>       </Statements>     </Batch>   </BatchSequence> </ShowPlanXML> 

Answer 2

Assuming that interpretation of missing impact % is identical or similar with that of avg_user_impact column from sys.dm_db_missing_index_group_stats system view then missing impact % represents (more or less):

Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.