Tuesday, 1 December 2009

How to enable and disable indexes

Whilst working to speed up a delete statement on a table of 6000000+ records it proved efficient to disable the table indexes, run the delete statement then enable the indexes again. If the indexes are left enabled there is a large overhead in rebuilding the indexes for each record that is deleted from the target table.

Disable an index:

alter index <<index name>> on <<table name>> disable

Enable an index:

alter index <<index name>> on <<table name>> rebuild

Please be aware the ALTER INDEX command is only available in SQL Server 2005 and above.

Also, SQL Server Management Studio doesn’t show the disabled status of an index within the main view so it is possible to disable an index and leave it disabled without realising.

More details on this from Decipher Info Sys

1 comment:

Improve SQL Server delete performance with views « Banshee Technologies said...

[...] It is also worth considering disabling any indexes related to the table you are deleting from as described here. [...]