Post reply

authgrowth log during rebuil index

  • January 13, 2021 at 11:36 am

    #3831886

    Good morning all ,

    Have any idea how I can reduce disk consumption during the reindexing phase

    I use the IndexOptimize procedure of the olla script

     

    Attachments:
    You must be logged in to view attached files.

  • January 13, 2021 at 1:47 pm

    #3831952

    It's completely dependent on the indexes. I know Minion lets you pick lists of indexes & break up the process. I suppose Ola's script must too. Do that. Do the big indexes first because they'll use the most disk space, then do the smaller indexes. However, the amount of space needed is the amount of space needed for a given index. You can't get away from this without making the indexes smaller.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 13, 2021 at 3:30 pm

    #3831985

    And you never want the log to autogrow during index maintenance because it's such a slow process.

    Instead, pre-grow the log to the total size you'll need.  If you really must, you can shrink the log after the index rebuilds finish.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 13, 2021 at 7:03 pm

    #3832080

    Grant Fritchey wrote:

    It's completely dependent on the indexes. I know Minion lets you pick lists of indexes & break up the process. I suppose Ola's script must too. Do that. Do the big indexes first because they'll use the most disk space, then do the smaller indexes. However, the amount of space needed is the amount of space needed for a given index. You can't get away from this without making the indexes smaller.

    Interesting suggestion and just a thought in that area.  I normally do the smallest indexes first (in order by page count of the leaf level) to recover disk space so that when I get to the largest indexes, there may be more free space and possibly be more contiguous free spaces that the larger indexes might enjoy a larger "avg_fragment_size_in_pages" and also leave less of an "unwanted free space" footprint.

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

  • January 13, 2021 at 7:49 pm

    #3832097

    Jeff Moden wrote:

    Grant Fritchey wrote:

    It's completely dependent on the indexes. I know Minion lets you pick lists of indexes & break up the process. I suppose Ola's script must too. Do that. Do the big indexes first because they'll use the most disk space, then do the smaller indexes. However, the amount of space needed is the amount of space needed for a given index. You can't get away from this without making the indexes smaller.

    Interesting suggestion and just a thought in that area.  I normally do the smallest indexes first (in order by page count of the leaf level) to recover disk space so that when I get to the largest indexes, there may be more free space and possibly be more contiguous free spaces that the larger indexes might enjoy a larger "avg_fragment_size_in_pages" and also leave less of an "unwanted free space" footprint.

    Totally agree.  My initial instinct, too, was to do the largest indexes first, but actual experience taught me that doing the smaller indexes first was actually much better in practice.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 14, 2021 at 9:37 pm

    #3833546

    abdalah.mehdoini wrote:

    Good morning all ,

    Have any idea how I can reduce disk consumption during the reindexing phase

    I use the IndexOptimize procedure of the olla script

    1. What unit of measure is your "Y" axis using on your chart?
    2. What is the size of the largest index that you're doing the index maintenance on?
    3. What is the Recovery Model for the database?
    4. If the answer to question 3 is "FULL", can excursions to the Bulk Logged recovery model be tolerated by the system?
    5. If the answer to question 4 is "Bulk Logged", have you ever taken a FULL backup in the FULL recovery model and are you doing regular log file backups?
    6. Which edition of SQL Server are you using?
    7. You also use the words "disk consumption" about the chart.  WHICH disk consumption??? MDF, NDF (can sometimes make a nice difference especially if in a separate file group to do a neat trick to conserve ), or LDF?

     

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

  • January 18, 2021 at 5:15 am

    #3834462

    I guess the OP has left the building. 😀

     

    --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 7 posts - 1 through 6 (of 6 total)

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