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 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
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) 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."
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) 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."
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply