Post reply

some questions related to Ola hallengren maintenance scripts

  • March 19, 2024 at 6:14 am

    Go to Answer

    #4397964

    Hi All,

    I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply.

    1. In which order the rebuild and update stats run. Is it based on tables sorted by name or something else?

    2. Is there a way to tell how much % of work is left over or how much % of work is done?

    3. Can we pause/suspend index rebuilds or updating stats operation and resume as per need?

    Thanks,

    Sam

  • March 19, 2024 at 8:44 am

    Answer

    #4398032

    1, if you set @Execute = 'N', it will print out the actions it will do in the order it will do it.

    2, there is no progress indicator, you could write your own wrapper on top, to do Execute=N, LogToTable=Y, then your code comes in picks up what it was going to do, and can update commandlog when it has done it, then you can see what you have done and what is left.

    3, yes use @resumable, assuming you are using a version and edition of SQL which supports the RESUME / PAUSE syntax.

  • April 26, 2024 at 9:47 pm

    #4415948

    It sounds like there is a particular index that may be taking a lot longer and blocking the processing of others , especially if you set a time limit on the operation. Refer to the commandLog to pinpoint any problematic maintenance on an index. You can remove this index and put it in its own SQL Agent job on its own day and time.

    ----------------------------------------------------

  • April 27, 2024 at 1:40 am

    #4415995

    vsamantha35 wrote:

    Hi All,

    I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply.

    1. In which order the rebuild and update stats run. Is it based on tables sorted by name or something else? 2. Is there a way to tell how much % of work is left over or how much % of work is done? 3. Can we pause/suspend index rebuilds or updating stats operation and resume as per need?

    Thanks, Sam

    Sam, this is going to sound like an incredibly stupid question but... Why are you doing index maintenance?  What do you think you're going to achieve for rowstore indexex with index maintenance?  Or, as Brent Ozar would ask, "What problem are you trying to solve"?

    --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)

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

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