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.

A-K
  • 16,804
  • 8
  • 54
  • 74
J.C.
  • 2,102
  • 1
  • 19
  • 28

4 Answers4

4

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?

Adrian Smith
  • 17,236
  • 11
  • 71
  • 93
  • Was just about to post the same thing. Definitely look at the execution plan to find out what is creating the bottleneck. – Phil Sandler Oct 21 '10 at 16:07
  • Good Point - I should have included this in my original post (I will update it). That being said, as per the execution plan, 70% of the operation is on the clustered index delete. The tables which are referenced almost all have a seek and none consume more than 3% of the overall query. – J.C. Oct 21 '10 at 16:09
  • @Phile: Jeremiah did a backup and restore and cannot reproduce the issue in local box, which means checking referential integrity along does not cause the issue. – Codism Oct 21 '10 at 16:12
  • Did you try changing the index to a non clustered one? – Mark PM Oct 21 '10 at 16:15
  • @Mark PM - I thought about doing this. I actually did this on my local PC and saw a great reduction in fragmentation - 16% vs 50%. I was unsure as to possible side effects. I am concerned because the 'main' table (1.5 million records), if you will, references this. What sort of fall-out might I expect? – J.C. Oct 21 '10 at 16:18
  • The biggest fallout I would expect would be from bad code that relied on the records being in pk order rather than using an order by clause. Is your pk an identity or is it natural key? I would never put a clusterd index on a natural key because it would cause a lot of moving around of records when things change. And of course you would have to pick a down time to do the change. You don't want to have users locked out while it is happening. – HLGEM Oct 21 '10 at 16:47
1

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

J.C.
  • 2,102
  • 1
  • 19
  • 28
  • This is good you share with us what you have decided to do. But you forgot to mention what are the results... – Gennady Vanin Геннадий Ванин Oct 26 '10 at 04:26
  • @vgv8 - Not a problem, I should've included that in my answer. Fragmentation actually increased from 50% to 66% (rebuild/reorganize can't get it any lower) and the time it takes to run the query dropped from about 2 minutes to 10 seconds (still way too high). The execution plan did not change. – J.C. Oct 28 '10 at 16:10
0

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

Codism
  • 5,928
  • 6
  • 28
  • 29
  • Thought about this as well -- ran profiler to identify possible locks, but to no avail. I have found nothing with locking at the moment. – J.C. Oct 21 '10 at 16:12
0

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

HLGEM
  • 94,695
  • 15
  • 113
  • 186