authgrowth log during rebuil index

  • 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.
  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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