时间戳

首页 > 解决方案 > 两个实例中相同数据库上的执行计划不同

sql - 两个实例中相同数据库上的执行计划不同

问题描述

我正在为一个神秘的问题而苦苦挣扎：我在生产中遇到了一个查询，它使他的性能急剧下降，从几秒钟到两分钟。通过分析执行计划，我发现它莫名其妙地不再对表中的某个字段使用非集群索引。我执行了以下所有步骤：

如果我强制查询使用索引（带有查询提示），它会立即完成。为什么sql server在执行计划中不考虑索引？

为了尝试重现问题，我在另一个 sql server 实例中恢复了相同的 db：结果是执行计划默认使用索引。

为什么在生产中仍然不使用索引？（硬件配置很好，服务器license是标准版）

查询是：

 SELECT Anagrafiche.[Ragione Sociale], Anagrafiche.IDAnagrafica
 FROM ([Registrazioni Magazzino]
 INNER JOIN Anagrafiche ON [Registrazioni Magazzino].IDCliente = Anagrafiche.IDAnagrafica) 
 INNER JOIN Movimenti ON [Registrazioni Magazzino].RifIDMovimenti = Movimenti.ID
 GROUP BY Anagrafiche.[Ragione Sociale], Anagrafiche.IDAnagrafica, Anagrafiche.Fornitore
 HAVING (((Anagrafiche.Fornitore)=1))
 ORDER BY Anagrafiche.[Ragione Sociale]

正确的执行计划是： 对

正确的计划

错误是： 错

错误的计划

表 [registrazioni magazzino] 包含大约 980.000 行。字段为 [idcliente]，索引为 [IX_Registrazioni Magazzino_2]

标签： sqlsql-serverperformanceoptimizationsql-execution-plan

解决方案

上的非聚集索引[Registrazioni Magazzino]不是覆盖索引，换句话说：仍有一些列需要通过键查找从聚集索引中取出。

因此编译器需要根据统计数据来决定是否使用索引并进行键查找；或者，考虑到行数，这些额外的键查找的成本是否不值得，而是应该对聚集索引进行扫描。

更复杂的是，由于实际上没有从 中选择任何列[Registrazioni Magazzino]，因此查询已转换为EXISTS查询，这本来应该是这样编写的。1 的 row-goal/TOP放置在连接的内侧，因此编译器认为它会很快得到结果，而忽略了进行如此多的扫描的巨大成本。

这里的解决方案很简单：将非聚集索引改为包含RifIDMovimenti列。您可以将其作为第二个键列执行，但我认为INCLUDE列会更好：

CREATE NONCLUSTERED INDEX [IX_Registrazioni Magazzino] ON [Registrazioni Magazzino]
  (IDCliente) INCLUDE (RifIDMovimenti)
  WITH (DROP_EXISTING = ON);

上的索引Anagrafiche (Fornitore, [Ragione Sociale]) INCLUDE (IDAnagrafica)也会有所帮助。这意味着Fornitore可以预先过滤，并且排序将从查询中删除。

关于您的查询的进一步说明：

  • 您应该使用别名，尤其是当您的表名带有空格时（无论如何都应该避免使用）
  • 如前所述，这实际上应该是一个EXISTS查询，没有GROUP BY
  • 简单的非聚合过滤器应该放在WHERE不HAVING
  • 不需要括号连接，它不会帮助或改变编译器所做的事情

这些更改不是必需的，但有助于查询理解，也可能有助于优化器。

因此，您的查询理想情况下会变成这样：

SELECT a.[Ragione Sociale], a.IDAnagrafica
 FROM  Anagrafiche a
 WHERE a.Fornitore = 1
   AND EXISTS (SELECT 1
      FROM [Registrazioni Magazzino] r
      INNER JOIN Movimenti m ON r.RifIDMovimenti = m.ID
      WHERE r.IDCliente = a.IDAnagrafica)
 ORDER BY a.[Ragione Sociale];

推荐阅读