• I didn't do a deep dive on this older article but I do disable indexes not only for deletes but also for index maintenance.  It's a good article to explain how to do that but, just like the author suggested, it's an introduction to what's possible and you do have to use your head. 

    Re-enabling previously disabled indexes are certainly one of those but, like the author said, "caveat emptor".  Pay attention.  If you have such things, then you need to add to the author's good base code to make sure that disabled indexes that are meant to continue to be disabled, stay that way (who the heck does such a thing for the long term anyway? :blink:)

    The other thing to be aware of is that you shouldn't disable unique indexes because they may be the target of FKs and disabling those indexes could screw FKs up (not to mention they could allow bad data in while they're disabled).  "Must look eye". 😀

    Once the caveats are understood, disabling indexes for deletes can really help performance a lot.  Disabling indexes can also help a whole lot if you have large indexes to maintain.  It won't help so much for the amount of time it takes but can keep you from unnecessarily blowing out your MDF file because, for any index of 128 extents (that's only 8MB, folks), the old index will persist until the new index is created and committed.  Once the new one is committed, the old one is dropped and creates possibly a huge amount of unwanted free space.  That's also a part of the reason why I tell folks to rebuild the index with the largest page count first... that will create enough reusable free space for the other indexes (thinking mostly clustered indexes here, which are typically not disabled prior to rebuilds).

    There's also the trick of doing a double-rebuild for the largest clustered index onto a separate file-group and back to keep huge amounts of unwanted free space from occurring in the MDF but the details of how to do that would make a better (and longer) article than a post.

    @joshua... nice article.  Thanks for taking the time to write it even if I'm seriously late in saying so.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)