January 13, 2021 at 1:47 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 13, 2021 at 3:30 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 13, 2021 at 7:03 pm
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
January 13, 2021 at 7:49 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 14, 2021 at 9:37 pm
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
--Jeff Moden
January 18, 2021 at 5:15 am
I guess the OP has left the building. 😀
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply