SQL Server 2008执行计划中“缺失索引影响％”的含义是什么？

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%.

我刚刚在SSMS中检查了估计的执行计划。我注意到查询的查询成本为99％（相对于批处理）。然后我检查了下面显示的计划。该成本几乎全部来自表A中的“聚集索引删除”。但是，缺失指数建议是针对表B.并且缺失指数影响据说是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)?

查询是一个DELETE语句（很明显），它依赖于嵌套循环INNER JOIN和TableB。如果根据计划的几乎所有成本都来自DELETE操作，为什么索引建议在表B上 - 即使它是扫描 - 只有0％的成本？ 95％的影响是否会影响扫描的可忽略的成本（列为0％），而不是查询的总体成本（据说几乎是所有批次）？

Please explain IMPACT if possible. Here is the plan:

如果可能，请解释IMPACT。这是计划：

4 个解决方案

#1

This is query 27 in the batch.

这是批处理中的查询27。

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

可能它显示的影响实际上属于一个完全不同的陈述（1-26）。

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

这似乎是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.

下面的两个批次包含相同的两个语句，顺序颠倒过来。请注意，在第一种情况下，它声称这两种陈述在99.38和第二次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.

我没有在实际的执行计划中看到这个问题，并且即使在估算的计划中，每个语句旁边的计划XML中也会显示正确的影响。

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)

我在此处添加了有关此问题的Connect项目报告。 （虽然可能你遇到了另一个问题，因为10％的影响似乎是计划中包含的缺失索引细节的截止点，很难看出这可能与问题中描述的原因相同）

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

批次1

SELECT *
FROM T1
WHERE X = -1

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

Batch 2

批次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（严重截断），表明正确的信息在计划本身中。

<?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>

#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):

假设缺失影响％的解释与sys.dm_db_missing_index_group_stats系统视图中的avg_user_impact列的解释相同或相似，则缺少影响％表示（或多或少）：

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.

如果实现了缺少的索引组，则用户查询可能会遇到的平均百分比收益。该值意味着如果实现了缺少的索引组，查询成本将平均下降此百分比。

#3

Thanks for the information everyone. Martin Smith I believe did find a bug as a result of this though I am not sure if it the same bug as what I am seeing. In fact I am not sure if my issue is a bug or by design. Let me elaborate on some new observations:

感谢大家的信息。马丁史密斯我相信确实发现了一个错误，虽然我不确定它是否与我所看到的相同。事实上，我不确定我的问题是错误还是设计。让我详细说明一些新的意见：

In looking through this rather large execution plan (62 queries), I noticed the the Missing Index recommendation (and respective Impact %) that i mentioned in the original question is listed on nearly every query in the 62 query batch. Oddly, many of these queries do not even call the table the index is recommended for! After observing this, I opened the XML and searched the element 'MissingIndexes' which showed about 10 different indexes missing all with varying Impact %'s, naturally. Why the execution plan does not show this visually and instead shows just one Missing Indezx, I do not know. I presume it is either 1) a bug or 2) it only shows the missing index with the HIGHEST impact % - which is the one I see riddled throughout my entire plan.

在查看这个相当大的执行计划（62个查询）时，我注意到我在原始问题中提到的缺失索引建议（以及相应的影响％）几乎列在62查询批次中的每个查询中。奇怪的是，许多这些查询甚至没有调用建议索引的表！在观察完之后，我打开了XML并搜索了元素'MissingIndexes'，它显示了大约10个不同的索引，它们都具有不同的Impact％，自然而然。为什么执行计划没有在视觉上显示，而是只显示一个Missing Indezx，我不知道。我认为这是1）一个错误或2）它只显示具有最高影响％的缺失索引 - 这是我在整个计划中看到的那个。

A suggestion if you are experiencing this too: Get comfortable with the XML over the visual execution plan. Search the xml element 'MissingIndexes' and match that up with the statements to get proper results.

如果您遇到这种情况也需要建议：在可视化执行计划中熟悉XML。搜索xml元素'MissingIndexes'并将其与语句匹配以获得正确的结果。

I also read from Microsoft http://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx that the missing index stats come from a group of DMVs. If the Impact % is in fact from these DMVs, then I would also presume that Impact % is based on MUCH MUCH more than just the Query/Statement in the execution plan were the index is recommended. So take it with a grain of salt, and use them wisely based your own knowledge of your database.

我还从微软http://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx上读到，缺失的索引统计数据来自一组DMV。如果影响％实际上是来自这些DMV，那么我也会假设影响％基于多个，而不仅仅是执行计划中的查询/陈述是推荐的索引。因此，请花一点时间，并根据您自己对数据库的了解明智地使用它们。

I am going to leave this opened-ended and not mark anything as an "answer" just yet. Feel free to chime in folks!

我将离开这个开放式的结果，并没有标记任何“回答”。随意欢呼大家！

Thanks again.

再次感谢。

#4

Okay so let me see if I can clarify here.

好的，让我看看我能否澄清一下。

There will still be costs to those other operations the 0% is because the DELETE on a loop is taking the vast majority of of your processor and IO time. That doesn't however mean those other operations don't have processor/memory/IO costs that can be improved on this query by adding that index. Especially if you are doing a loop essentially your mapping to tableB for one record then deleting out of tableA over and over. Therefore having an index that makes it easier to match those rows will speed up your delete.

那些其他操作仍然会有0％的成本，因为循环上的DELETE占用了处理器和IO时间的绝大部分。但这并不意味着那些其他操作没有处理器/内存/ IO成本，可以通过添加该索引来改进此查询。特别是如果你正在做一个循环，基本上是映射到tableB的一个记录，然后一遍又一遍地删除tableA。因此，拥有一个可以更容易匹配这些行的索引将加快删除速度。