SQL Server - PK删除性能差

Question

我在SQL Server 2008 R2中有一张表，大约有400行数据（非常少），它的主键是一个自增列，有聚集索引。该表被其他13个表通过引用完整性关联（没有级联删除或更新）。插入/更新/获取几乎是瞬间完成的 - 我们谈论的是分秒之间（应该是这样的）。然而，使用主键进行删除需要长达3分钟的时间，我从未见过快于1.5分钟的情况：

DELETE FROM [TABLE] WHERE [TABLE].[PK_WITH_CLUSTERED_INDEX] = 1

索引被严重碎片化 - 90％。我已经重建和重新组织了该索引（以及该表上的其他索引），但我无法将其降至50％以下。

此外，我对数据库进行了备份/还原到我的本地PC上，删除没有任何问题 - 少于一秒钟。

我尚未完全删除并重新添加聚集索引。这本身就是一个问题，因为当其他表引用PK索引时，SQL Server不允许您删除它。

有什么想法吗？

更新

我应该在原始帖子中包含这个。执行计划将“责任”归咎于聚集索引删除 - 70％。在引用此表的13个表中，执行计划表示没有一个表超过整个查询的3％ - 几乎所有表都使用索引查找。

- J.C.

---

我现在感到困惑不已。这真的很令人沮丧。 - J.C.

Answer 1

如果您删除一行数据，数据库必须检查这13个表中是否有任何一个引用了该行数据。那么，引用您正在删除的表的其他表的外键列上是否有足够的索引呢？

- Adrian Smith

---

刚要发布同样的内容。一定要查看执行计划，找出是什么造成了瓶颈。 - Phil Sandler

好的，我应该在原始帖子中包含这个（我会更新它）。话虽如此，根据执行计划，70%的操作是在聚集索引删除上。几乎所有被引用的表都有一个查找，没有一个消耗超过总查询的3%。 - J.C.

@Phile：Jeremiah 进行了备份和恢复操作，无法在本地环境中重现该问题，这意味着仅检查引用完整性并不会导致该问题。 - Codism

你尝试将索引更改为非聚集索引了吗？ - Mark PM

@Mark PM - 我考虑过这个问题。实际上，我在我的本地电脑上做了这件事，并看到了很大的碎片减少-16%对比50%。但我不确定可能会有什么副作用。我担心因为“主”表（150万条记录）引用了这个表。我应该预计什么样的后果？ - J.C.

我预计最大的影响将来自于依赖于记录按主键顺序排列而不是使用 "order by" 子句的糟糕代码。你的主键是标识还是自然键？我永远不会在自然键上放置聚集索引，因为它会在事物变更时导致大量记录移动。当然，你必须选择一个停机时间来做出更改。你不希望用户在此期间被锁定。 - HLGEM

Answer 2

好的，我有一个答案...

首先，我基本上用问题中提到的所有选项以及相关答案来尝试解决这个看似微不足道的问题，但都没有成功。

我决定采取以下措施：

1. 添加一个临时唯一索引（这样SQL Server就允许我删除聚集索引）
2. 删除聚集索引。
3. 重新添加聚集索引。
4. 删除临时唯一索引。

本质上，我清除并重新添加了聚集索引。我能从中得出的唯一结论是，也许索引的某个部分或其物理存储位置已经“损坏”（我使用这个术语并不严谨）。

- J.C.

---

这很好，你和我们分享了你决定做什么，但是你忘记提到结果是什么了... - Gennady Vanin Геннадий Ванин

@vgv8 - 没问题，我应该在我的答案中包含那个。实际上，碎片化从50%增加到了66%（重建/重组无法使其更低），运行查询所需的时间从大约2分钟降至10秒（仍然太高）。执行计划没有改变。 - J.C.

Answer 3

可能在生产环境中，该表被另一个耗时的进程锁定了。

- Codism

---

我也考虑过这个问题——运行分析器来识别可能的锁定问题，但没有找到任何相关信息。目前我还没有发现与锁定有关的内容。 - J.C.

Answer 4

另一个想法，这个表上有删除触发器吗？它可能会导致问题吗？

- HLGEM

---

好观点，但我也已经考虑过了 - 没有触发器。 - J.C.