SQL Server - Poor performance of PK delete

Question

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.

Answer 1

If you delete a row, the database must check that none of the 13 tables references that row. Are there sufficient indexes on the foreign key columns on those other tables that reference the table you are deleting from?

Answer 2

It will need to check those 13 tables.

Also, with that delay I imagine some of those tables have thousands of rows in so its reading every row in those tables to check to see if they reference that deleted row.

Execution Plan should establish where you losing the time. Espically look for the thicker arrows where a lot of data is being passed.

EDIT: Also if you havent already try sp_updatestats to UPDATE STATISTICS

Answer 3

Maybe the table is locked by another time-consuming process in production.

Answer 4

Another thought, is there a delete trigger on the table? Could it be causing the issue?

Answer 5

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).