Enabling indexes

  • If I disable an index for a large insert/update/delete, what happens when it's enabled again? I know the stats are kept, but is it a performance gain if SQL has to update the index after it's enabled?

    There is an exception to every rule, except this one...

  • After you disable an index, you will need to rebuild it in order to use it again. Disabling drops all the storage for an index.

    Eddie Wuerch
    MCM: SQL

  • Which is more costly, rebuild or create?

    There is an exception to every rule, except this one...

  • Keep your data catalog correct and disable an index if you want to re-enable it later!

    This way, persons investigating "troubles" will know someone performed this action on purpose ( as the index is still in the catalog )

    and the index will be put back to active at a later time.

    Do not disable a clustered index, as that will put your table "offline".



    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Rebuilding a disabled index does the same work as originally creating it.

    The difference between ALTER INDEX ... DISABLE / ALTER INDEX ... REBUILD and DROP INDEX / CREATE INDEX is only that the index definition is retained when it is disabled, so you can re-create the index by simply rebuilding it. They cost the same.

    Eddie Wuerch
    MCM: SQL

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply