SQL Server - 性能较差的PK删除 [英] SQL Server - Poor performance of PK delete

问题描述

我在SQL Server 2008 R2中有一个由约400行组成的表（几乎没有） - 它在主键上有一个聚簇索引（这是一个标识）。该表通过引用完整性（没有级联删除或更新）引用约13个其他表。

插入/更新/获取几乎是即时的 - 我们在说一个拆分第二（应该预期）。但是，使用PK的删除需要3分钟，我从未见过它超过1.5分钟：

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

索引严重碎片 - 90 ％。我重建和重组了这个索引（连同其他表格），但是我不能低于50％。

此外，我做了一个备份/恢复数据库到本地PC，我没有删除问题 - 不到一秒。

我没有做的一件事是完全删除聚集索引并重新添加。这本身就是一个问题，因为SQL Server不允许您在其他表引用时删除PK索引。

任何想法？ p>

更新

我应该将其包含在我的原始帖子中。执行计划对集群索引删除占70％。在引用该表的13个表中，执行计划表示没有超过总查询的3％以上 - 几乎全部命中索引。

解决方案

嗯，我有一个答案...

首先，我几乎耗尽了上述问题中指出的所有选项以及关联答案。我没有运气，似乎是一个琐碎的问题。

我决定要做的是：

1. 添加临时唯一索引（所以SQL
   服务器将允许我删除
   聚簇索引）


2. 删除聚簇索引。


3. 重新添加聚集索引。


4. 删除临时唯一索引

本质上，我擦除并重新添加了聚簇索引。我唯一可以从中消除的是，索引的一部分或物理存储的位置被损坏（我使用该术语很宽松）。

I have a table in SQL Server 2008 R2 consisting of about 400 rows (pretty much nothing) - it has a clustered index on the primary key (which is an identity). The table is referenced via referential integrity (no cascade delete or update) by about 13 other tables.

Inserts/Updates/Gets are almost instant - we're talking a split second (as should be expected). However, a delete using the PK takes as long as 3 minutes and I've never seen it faster than 1.5 minutes:

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

The index was heavily fragmented - 90%. I rebuilt and reorganized that index (along with the rest on that table), but I can't get it below 50%.

Additionally, I did a backup/restore of the database to my local PC and I have no issues with deleting - less than a second.

The one thing I have not done is delete the clustered index entirely and re-add it. That, in and of itself is a problem, because SQL Server does not allow you to drop a PK index when it is referenced by other tables.

Any ideas?

Update

I should have included this in my original post. The execution plan places 'blame' on the clustered index delete - 70%. Of the 13 tables that reference this table, the execution plan says that none exceed more than 3% of the overall query - almost all hit on index seeks.

解决方案

Well, I have an answer...

First off, I pretty much exhausted all options indicated in the question above along with the associating answers. I had no luck with what seemed like a trivial problem.

What I decided to do was the following:

1. Add a temporary unique index (so SQL Server would allow me to delete the clustered index)
2. Delete the clustered index.
3. Re-add the clustered index.
4. Delete temporary the unique index.

Essentially, I wiped and re-added the clustered index. The only thing I'm able to take away from this is that perhaps part of the index or where it was physically stored was 'corrupted' (I use that term loosely).

这篇关于SQL Server - 性能较差的PK删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！