Post reply

Maintenance Plan - Rebuild Indexes only run if required or open fires on schedule?

  • August 23, 2015 at 10:37 pm

    #319981

    Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not. Just want to understand this.

    Shamshad Ali

  • August 23, 2015 at 11:46 pm

    #1821880

    shamshad.ali (8/23/2015)

    Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not. Just want to understand this.

    Shamshad Ali

    I'm pretty sure that if you use a maintenance plan to rebuild and reorganize indexes, it's going to rebuild and reorganize ALL of them even if they don't need it. And, it's going to do it twice if you have both rebuild and reorganize in your MP.

    --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)
    Intro to Tally Tables and Functions

  • August 24, 2015 at 1:30 am

    #1821889

    Hi

    You might want to check out the index maintenance script from this site. https://ola.hallengren.com/

    This stored procedure will allow you to set corrective actions based on the %fragmentation level of your indexes.

    You may check the link below for index maintenance guidelines.

    https://msdn.microsoft.com/en-us/library/ms189858.aspx

  • August 24, 2015 at 4:35 am

    #1821902

    Without seeing the query, I can't tell what you're running. As Jeff said, if you're using the Maintenance Plan wizard to do this, it's running for all indexes.

    For index maintenance, I'd suggest you take a look at Minion Reindex[/url]. It's a free tool.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 4 posts - 1 through 3 (of 3 total)

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