We have 5 TB database and the rebuild index job is taking days to complete. Is there a way to rebuild indexes on chunks of tables/indexes. For eg, rebuild index on first chunk, then on the next chunk and so on. Please let me know
August 27, 2020 at 2:30 am
The best way to rebuild indexes is not to do it.
You need to work on your index strategy. It's named "strategy" for a reason. Correct approach to indexing structure pays in the long run.
Correct indexing strategy will make the population of indexes non-disruptive, causing insignificant index fragmentation.
Fragmentation by itself is not always such a big deal, as it's pictured by some sources. Minor index "imperfections" might be simply ignored, as they would not cause any noticeable performance degradation.
So there would not need for re-indexing of any kind at all.
_____________
Code for TallyGenerator
August 27, 2020 at 5:51 am
What is your current Index Rebuild strategy and how frequently and in what % the fragmentation is happening?
August 27, 2020 at 6:17 pm
What is your current Index Rebuild strategy and how frequently and in what % the fragmentation is happening?
To Sergiy's point, asking about the % of Fragmentation is the first problem that people have in their Index Maintenance strategy.
The first question that should be asked, especially with such a database of the 5TB size is <insert drumroll here>... is the fragmentation actually causing any performance issues?
The second question would be to ask, has the fragmentation resulted in low page density, which wastes both memory and disk space?
The third question to ask is what will happen after you fix the fragmentation? Most people never make the connection but when does your server seem to have the largest problem with performance? Just because of some nasty personal experience, I'd have to say that it's usually going to be the "morning after" the index maintenance ran. It would take way to much to explain that on a post thread but I can summarize it in three words... Massive Page Splits.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2020 at 4:37 am
No, on a non-partitioned table, there is no way to build chunks / parts of indexes (frankly I think there should be, but there isn't).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 28, 2020 at 6:03 am
No, on a non-partitioned table, there is no way to build chunks / parts of indexes (frankly I think there should be, but there isn't).
Filtered index?
_____________
Code for TallyGenerator
We have 5 TB database and the rebuild index job is taking days to complete. Is there a way to rebuild indexes on chunks of tables/indexes. For eg, rebuild index on first chunk, then on the next chunk and so on. Please let me know
So, the answer to your question is, no... you can't REBUILD indexes in chunks unless they are partitioned and partitioned tables can actually hurt performance a fair bit depending on how they're used.
I know it sounds like we're pushing you off but we're not. I was serious about the three questions that I asked in my previous post. If you could start with answers to those and a few more that will follow, we might be able to help.
Another question that has been asked is "What is your index maintenance strategy"? For example, are you following the supposed "Best Practices" of using REORGANIZE at 5-10 % to 30% fragmentation and REBUILD for anything above 30%? I've found that's generally a bad idea but especially on databases the size of yours.
I'd also have to say that if you're using REORGANIZE, you might actually be perpetuating fragmentation which means that once you're done reorganizing a given index, the data will be organized in such a fashion as to actually cause more page splits (the primary cause of fragmentation but not the only one) than if you had done nothing to it.
And, do you have any form of replication going on that depends on the database remaining in the FULL recovery model/content of the log file?
Even after knowing the answers to those questions, it's actually impossible to make even a decent recommendation based on so little information. Indexes generally suffer independently (there are exceptions) and we'd need a whole lot more knowledge about the indexes at the index level to be able to help. Are you allowed to provide such information? Probably not... and the people that have posted on this thread know all that, as well.
That means that we can't provide guidance for you to build a complete solution and, I'll say it again, the supposed "Best Practices" are the worst thing you can do to your indexes.
So, to start, take a look at the original 3 questions I posted and you that a starting point to begin fixing, say, you 5 largest indexes. If you're doing index maintenance of any kind on indexes that have a "0" fill factor, stop it. It means you don't know what the index is doing and using REORGANIZE or REBUILD on them is not only perpetuating fragmentation, it's probably making page splits and the related performance problem and the related rate of fragmentation far worse. Spend most of your time making sure your statistics stay up to date based on the number of rows updated.
And, no... I'm not making any of this stuff up. I went for almost 4 years without general index maintenance. My databases are only 1-2TB but I'm thinking that's close enough for a bit of a comparison. The only thing I did was to recover disk space by doing rebuilds on the large indexes that had low page densities due to fragmentation. Some you can rebuild at 100%... others, not. Either way, you have to know how the index is actually behaving before you do such a thing.
Then, you can start working on what's causing the fragmentation and possibly fix it using defaults and the occasional datatype change, and a handful of other tricks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply