主页 / dba / 问题 / 321074
Accepted
bigchief
Asked: 2022-12-17 04:14:34 +0800 CST

为什么删除冗余索引会导致应用程序意外执行？

  • 772

我继承了多年前软件供应商安装的一个应用程序（和关联的 MS SQL 数据库）。我们目前没有任何类型的供应商支持。除了使用应用程序之外，我们还独立于应用程序访问数据库，经常直接更新数据库并查询数据以进行报告。

为了提高性能，我删除了一些不必要的索引并创建了其他索引。这会导致应用程序在执行某些任务时出现错误。该应用程序不显示任何类型的任何错误消息，并且似乎正常执行，但应用程序未按预期更新数据库。我无权访问源代码。

我的理论是应用程序在其查询之一的 with(index) 语句中明确指定了一个已删除的索引。这将导致服务器返回错误而不是完成查询，如果应用程序抑制错误，用户将不会意识到某些表已更新而其他表未更新。还有什么其他想法可能导致这种行为？

假设我的理论是正确的，我仍然认为索引实际上是不必要的，应用程序不应该指定索引。也许一种解决方法是创建一个具有相同名称但列数尽可能少的新索引。有没有办法创建没有列的索引？使用与聚集索引相同的列创建非聚集索引是否最有效？优化器在这种情况下做了什么——它是否仍然计划包含这些仅引用聚集索引的“坏”索引，或者它是否知道忽略 with(index) 请求？

我确实运行了跟踪以查看应用程序正在运行哪些查询。我得到了一堆 RPC 调用，如“exec sp_cursorfetch”和“exec sp_cursorexecute”（我不明白）而不是实际的 SQL。也许我会在一个单独的问题中询问这些，但如果您知道我如何将这些语句解释或解码为常规 SQL，那么这至少可以让我证实我的理论。

sql-server

3 个回答

  1. Best Answer

    要验证您关于应用程序代码隐藏错误的理论，请运行 XE 跟踪以捕获error_reported事件。

    sp_cursor*RPC 调用是系统游标过程，通常由服务器端游标的客户端驱动程序调用。sp_prepare在传递 SQL 语句并返回句柄以供后续使用的同一连接上应该有一个先前或类似的 RPC 调用。

    索引更改不会改变编写良好的应用程序的行为，但对于那些做出糟糕假设的应用程序可能会有意想不到的副作用。例如，考虑ORDER BY下面查询中的缺失可能会返回任意行。返回的行将根据查询计划中使用的索引而有所不同。

    SELECT TOP (1) Column1 FROM SomeTable WHERE Column2 = @SomeValue;

    如果可能，我建议您在测试环境中执行以下操作：

    1. 恢复原始索引并验证正确的行为
    2. 删除冗余索引并再次验证
    3. 逐步引入新索引
    • 6

  2. 除了其他人所说的，您可能会发现 RedGate 的SQL 搜索工具有助于查找对应用程序的数据库代码WITH(或WITH(DroppedIndexName)在数据库代码中的引用。

    如果您的理论是正确的，那么这些可能是某些非事务性调用堆栈的一部分，其中发生错误，未完全/正确回滚，并在应用程序端被抑制。

    • 2

  3. 使用与聚集索引相同的列创建非聚集索引是否最有效？

    要以这种方式正确模拟聚集索引，您需要INCLUDE所有非索引列以及覆盖相同的列。这在空间方面效率低下，因为您要在该索引中添加所有表数据的完整额外副本，并且会影响插入/更新性能（尽管对于许多读取密集型的用例来说，这并不像您可能认为的那么重要思考）。

    如果您不INCLUDE使用伪造的聚簇索引，那么您可能会损害任何专门引用它的查询的性能，因为您现在可以查找回聚簇索引以查找新索引未涵盖的列。

    优化器在这种情况下做了什么——它是否仍然计划包含这些仅引用聚集索引的“坏”索引，或者它是否知道忽略 with(index) 请求？

    我假设它会按照它所说的那样做并使用指定的索引，只有在您不需要INCLUDE所有内容时才引用 CI。这应该很容易设置一个示例测试环境来验证。

    为了提高性能，我删除了一些不必要的索引

    删除不必要的索引通常只会帮助解决惰性/更新性能问题（如果您经常对所有索引执行此操作，则通过不占用备份空间或重建/重新组织来减轻维护负担）所以如果读取性能是您的关注点把它们放回原来的样子。

    是最有效率的？

    如果没有其他复杂因素，将原始索引恢复原样将是您最有效的解决方案 IMO，无论您是否保留新索引（因为它们可能有助于其他访问）。

    还有什么其他想法可能导致这种行为？

    引用已删除索引的强制计划是否仍会被使用并因此在制定计划时导致错误？这是一个我没有使用过的功能，所以只是把它扔在那里作为研究的可能原因。

    我见过一个显式引用明显不需要的索引的情况是无论如何都会进行扫描，因此创建了一个仅包含所需列的索引，以便扫描比完整表小的内容，但查询计划程序会尝试其他内容无论如何（首先点击其他表并用该表中的大量搜索替换扫描）结果是更耗时。这通常是错误查询设计的标志（一些不可搜索的东西可以变成可搜索的）或者只是数据结构和需要从中获取的报告的不匹配，尽管可能表明可能导致错误的索引统计信息，但如果不参考代码，您将无法检查（并且可能无法安全地对此做任何事情）。也可能是不再需要的东西，

    此外，正如 Dan 的回答中所指出的，有人可能正在使用可怕的反模式来尝试通过索引选择而不是显式来控制排序ORDER BY，或者在不知不觉中依赖于特定索引使用（没有明确提示）产生的顺序，其中显式ORDER BY确实应该给出但以前从未需要过（在后一种情况下，即使您将原始索引放回原处，将来也可能会出现类似的问题，现在可能是考虑日落并更换该软件的时候了！） .

    • 1

相关问题