Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes

  • Joshua Feierman

    SSC Eights!

    Points: 903

    Comments posted to this topic are about the item Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes

  • nugentgregg

    SSC Rookie

    Points: 29

    There are a number of online expert. The expert in Database management system like SQl and others. From online any one can choose the expert for a good skilled writing experts choose the best thesis writing service provider from online.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Makes total sense, good to know. Thanks.

  • g.britton

    SSChampion

    Points: 13685

    In the WHERE clause you have:

    (sobj.name = @i_Table_Name or @i_Table_Name = '')

    Since @i_Table_Name is defined as sysname, you might want to use N'' instead of just ''.

    BTW did you consider using

    (sobj.name like @i_Table_Name )

    instead, thus allowing for wildcards and then covering the empty string with N'%' instead of just N''?

    I often find that explicit is better than implicit.

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Joshua Feierman

    SSC Eights!

    Points: 903

    Hi Gerald, two excellent suggestions, which I'll implement.

  • pcindex

    SSC Rookie

    Points: 40

    will this enable ALL disabled indexes?

    if so, there is a flaw, as if we already have a few disabled indexes (out of use)

    and we try the disable/re-enable routine,

    it will re-enable those ("out of use") ones too.

    we somehow need to "save" which ones we are disabling in the routine,

    and then re-enable just those.

    regards,

    Makis

  • Joshua Feierman

    SSC Eights!

    Points: 903

    Hi Makis,

    That's an interesting suggestion. I'll have to think about that one a bit, as it would require having a table somewhere where the indexes that the procedure initially disables are stored, and I was trying to avoid having any footprint beyond a procedure. Do you envision it as keeping that information stored until you run it in enable mode, then deleting it? What about if multiple processes are calling it at the same time, say for different databases or sets of tables; how do you think it should handle that?

    Thanks for the feedback in any case.

    -Josh

  • Stephen Randolph

    SSC Enthusiast

    Points: 105

    If you’re using any type of version control be careful with doing anything more than enabling the index or risk having that change removed and the index rebuilt during your next deployment.

  • Rick-153145

    SSCrazy

    Points: 2683

    Joshua Feierman - Friday, October 23, 2015 5:54 PM

    Hi Makis,That's an interesting suggestion. I'll have to think about that one a bit, as it would require having a table somewhere where the indexes that the procedure initially disables are stored, and I was trying to avoid having any footprint beyond a procedure. Do you envision it as keeping that information stored until you run it in enable mode, then deleting it? What about if multiple processes are calling it at the same time, say for different databases or sets of tables; how do you think it should handle that?Thanks for the feedback in any case.-Josh

    Why not have a table with all disabled indexes in? You could even write this into a database or server trigger so it wouldn't be a manual operation, then just don't re-enable any that are in the table.

    EDIT: You would obviously need to disable the trigger when you run this process, but it only needs to be for the initial disable of the indexes.

  • Jeff Moden

    SSC Guru

    Points: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    Has anyone done this on servers with numerous applications accessing them? How do you handle applications slowing down due to the loss of indexes? And how does Always On react to rebuilding several large indexes at once? Long ago I used to use maintenance plans and quit because rebuilding every index of a large table created a storm of AlwaysOn replication that also screwed up replication.

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

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