August 4, 2021 at 6:50 pm
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...
August 4, 2021 at 8:57 pm
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
August 4, 2021 at 9:05 pm
Which is more costly, rebuild or create?
There is an exception to every rule, except this one...
August 5, 2021 at 7:54 am
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
August 5, 2021 at 3:50 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply