最近我们对我们的生产数据库运行了一个脚本，该脚本将动态删除并重新创建数百个索引作为过滤索引。虽然此脚本在之前的所有其他测试中运行良好，但在这次测试之后，SQL Server 查询计划缓存表现异常。
执行计划器显示错误使用了错误的索引，返回了数百万行，而实际上只有少数应该匹配。当索引正确时，执行计划显示 SQL Server 决定使用索引 Scan 而不是索引 Seek 提供远低于最佳结果的索引。在适当的位置使用 WITH(INDEX(indexname)) 或 WITH(FORCESEEK) 之类的表格提示，可以更正此问题。INNER LOOP JOIN 也修复了其中的一些问题。
然而，问题在于，即使像以前一样删除并重新创建这些新的过滤索引，查询计划仍然保持不变。查询计划缓存已被清除，数据库已恢复到不同的环境，统计数据已更新，显然索引已重建，因此它们不会碎片化。
目前这是一个关键问题，没有人知道如何解决。虽然我们可以强制 SQL Server 使用正确的计划，但它根本不是解决必须使用它更新的大量软件的方法，而且显然需要手动指出如何处理查询的数据库不是一个选项.
因此，我们将不胜感激任何帮助。
编辑： 我们设法修复了一个查询，方法是删除索引，重新创建过滤后的索引，然后使用 FULLSCAN 运行 UPDATE STATISTICS 表名。这解决了一些问题，并且两个连接正常工作。在此之后，我们必须对原始索引脚本中完全没有涉及的多列索引进行单独的更改，以包括连接中使用的列之一。这两个更改一起使查询计划器能够解决使用 Seek 而不是 Scan 的正确索引。
现在的理论是，由于较早的崩溃，数据库显然是通过删除底层数据库然后从备份创建一个新数据库来恢复的，而不是像以前那样仅使用 REPLACE。这会以某种方式断开 masterdb 的元数据，例如执行计划、所有缓存以及来自数据库的诸如此类的东西，从而导致新的海量数据库没有现有的计划来处理查询。这与新创建的索引上明显失败的统计信息更新组合在一起，会产生这样一种情况，即 SQL Server 不知道如何解决它被轰炸的各种查询。
然而，我不相信这仍然足够，因为一些行为，例如必须改变多列索引，以及在过去的任何先前测试环境中都不需要统计更新的事实 2几个月的测试，似乎表明还有其他地方出了问题。
好了，现在问题解决了：
虽然使用过滤索引（NOT NULL）似乎合乎逻辑，以减少数据库大小，并且正如网络上的许多来源所说，提高性能，但现实似乎完全是另外一回事。
用外行的话来说，SQL Server 查询规划器甚至可以解析基本的内部联接，而无需对列的内容做出任何假设。即使 NULL 值不构成连接，它们也必须包含在列索引中以便查询计划程序使用它，除非另外指定谓词，例如 WHERE joinCol_ID IS NOT NULL。基本上，SQL Server 根本不使用过滤索引进行连接，除非修改查询本身以说明过滤值。相反，它将在这些列上创建新的统计信息和/或使用聚簇索引扫描或其他包括该列的索引，以它认为最有效的为准。因此，在外键上使用过滤索引绝对是一个可怕的想法。
我们仍然不知道在多个其他环境中进行了几个月的测试，却从未在这个单独的数据库之外产生相同的结果，但这就是它应该工作的方式。显然，据我们所知，一些与缓存、统计信息或配置无关的东西导致生产数据库表现不同并正确检测和使用过滤索引，而所有测试环境只使用旧索引（见索引被删除并以相同的名称重新创建，这似乎是一个有效的理论，即使没有真正的证据）。
所以这个故事的教训是：网络上到处都是过滤索引未被充分利用的例子，它们是多么的棒。但是这个严重的缺点从来没有出现过，只是作为一个在我脑后挥之不去的念头说“如果这些这么好，那为什么默认情况下不从索引中过滤掉 NULL 值，因为它们只占用空间并且只服务于特殊情况下的目的”？好吧，现在我知道为什么了。:)