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

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>