October 26, 2021 at 3:44 pm
We run index rebuild once a week using ola hallengren scripts. However, I see the fragmentation is over 30%. Since it's exceeding that threshold should I run more often than once a week?
October 26, 2021 at 3:56 pm
Is it causing you query performance problems?
If yes rebuild statistics
If yes and rebuild statistics didn't work rebuild index
If no leave it
This day and age you want to be more concerned with keeping statistics updated than removing fragmentation, especially since everything should be on SSD's now
https://www.youtube.com/watch?v=qfQtY17bPQ4
I know this is about GUID's but the detail about fragmentation applies across all data types
October 26, 2021 at 8:35 pm
First of all, if you've done a REBUILD on an index and you very quickly exceed 30% logical fragmentation, then you're ACTUALLY CREATING A PERFORMANCE ISSUE and it can be quite serious. There is only 1 type of insert pattern (caused by "Sequential Silos") that will cause fragmentation without doing page splits. In all other cases, Page Splits are involved and they cause a shedload of blocking and log file usage, both of which are performance issues.
Rebuilding more often may actually make the problem worse, especially if you don't have the correct Fill Factor assigned. And, don't forget, lowering the Fill Factor may not actually do anything to prevent the fragmentation and will only make your queries slower. For example, a 70% Fill Factor realized over most of your Clustered Index will actually cost you (100/70-1)*100 or 42.8% more memory, more disk space, and more time for scans (full or partial) to run. That's a horribly high price to pay if lowering the Fill Factor doesn't actually prevent or at least significantly delay the fragmentation.
What kind of index would be so screwed? The answer is any "ever-increasing" keyed index which has rows inserted and then they're updated in an "ExpAnsive" updates (where individual rows grow in size) before you do another index rebuild at a lower Fill Factor. The problem occurs because the new rows "go in" on the last page of the index and the inserts DO NOT follow the Fill Factor. They end up filling the pages to being as full as they can get for the given row sizes and have almost no room for "ExpAnsive" updates. When the "ExpAnsive" updates occur on just a few rows per page (maybe even just one!), then BOOM! Massive fragmentation occurs to every page that has been updated.
The ONLY reason to rebuild such tables is to recover disk space after the period of that "Hot Spot" has passed.
The bottom line is that if you don't actually know the reason why and exactly how the fragmentation is occurring, you may actually be causing more damage than what you're fixing (ESPECIALLY IF YOU"RE USING REORGANIZE, which doesn't work the way you probably think it does).
I totally agree with "Ant-Green" above... spend more time on updating statistics and doing DBCC CHECKDB. I'm living proof that it works. While I don't recommend it (because of low page densities), I went 4 years without doing any index maintenance with the exception of occasionally rebuilding a couple of the larger tables and there was no performance degradation. In fact, performance got better during the first 3 months and then stayed the same for 4 years.
The bottom line is that doing no index maintenance based on logical fragmentation is better than doing it incorrectly... and if you're using REORGANIZE between 5 and 30%, you're probably doing it wrong
I actually proved that REORGANIZE is the main reason why Random GUIDs fragment so badly and then demonstrated how inserting 100 THOUSAND rows per day into a Random GUID key index for 58 days (5.8 MILLION rows total) resulted in less than 1% fragmentation until the last instants of the 58 day period.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply