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".
Johan
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