Should i disable clustered index before deleting data?

Question

i know this has been discussed already like anything but could not find a reliable answer i can go with.

Suppose i have a table with 10 billion records and need to delete records with identity column in where clause. which option should i go with?

option 1: disable the index which will save overhead to rearrange the index after deletion but will take longer time to search which row needs to deleted.

option 2: will not do anything with index which will locate the row very fast but rearrange the index can take some time.

i am more inclined towards the 2 option but want to see what will experts say? :)

Thanks Martin for Prompt reply and sorry to give huge data size for my example but all i want is to understand logic, will it be the same if table has only thousands rows where row can be identified in a heap — tinks, Jun 05 '13 at 14:36
Also see http://stackoverflow.com/q/2126434/27535 It depends on if you deleting a few rows or billions — gbn, Jun 05 '13 at 14:40
If you disabled a table's clustered index, it disables access to the table; you can, however, disable nonclustered indexes and carry out deletes, then re-enable them (via `REBUILD`) which is often more performant during large deletes. — tommy_o, Jun 05 '13 at 17:38

score 10 · Accepted Answer · answered Jun 05 '13 at 14:35

10

Suppose i have a table with 10 billion records and need to delete records with identity column in where clause. which option should i go with?

If you're deleting (or inserting) more than 10% of the table (1 billion records), you should remove all of the non-clustering indexes, delete the records, then rebuild the non-clustering indexes.

If you're deleting less than 10% of the table, leave the indexes in place.

You're free to do performance testing to see if the 10% rule applies to your SQL Server database engine.

answered Jun 05 '13 at 14:35

Gilbert Le Blanc

50,182
6
67
111

Thanks for giving this 10% border Gilbert – tinks Jun 06 '13 at 04:23
Do you mean "you should disable" instead of "you should remove"? – Andrei Sura Nov 16 '16 at 17:57
1

@Andrei Sura: Nope. I mean remove or delete. You have to rebuild the indexes after you're finished deleting or inserting rows. – Gilbert Le Blanc Nov 16 '16 at 19:18
What about high DTU usage when rebuilding the indexes? – Enrico Jul 28 '23 at 08:53
@Enrico: You'll have the high DTU usage updating all the indexes as you delete. As always, perform measurements of your particular database system to determine what method works best when deleting massive numbers of rows. – Gilbert Le Blanc Jul 28 '23 at 11:47

score 9 · Answer 2 · answered Jun 05 '13 at 14:32

"Option 1" is not an option anyway.

Disabling the clustered index will make the whole table inaccessible and you would not be able to run a DELETE on the table anyway. It would fail with

The query processor is unable to produce a plan because the index ... is disabled.

Example code generating this error.

CREATE TABLE T(X INT CONSTRAINT PK PRIMARY KEY CLUSTERED, Y INT);

ALTER INDEX PK ON T DISABLE

DELETE FROM T

score 0 · Answer 3 · answered Jun 05 '13 at 14:30

You generally delete indexes for any bulk inserts into the tables, that too for temporary duration. Once the bulk insert is done, the indexes are created back.

Indexes would help locate the record easily for update/delete operations and you would never want to remove them in such cases.

HTH.

Should i disable clustered index before deleting data?

3 Answers3

Linked