(so there is no "standard" practive, I agree)
I'm in the process of trying to create one. Some of it is rock solid and some of it is still a work-in-progress. You've identified a part of it. Static/nearly static tables and "ever-increasing" indexes that suffer no "expansive" updates. I rebuild the static tables at 100% so I can write code that knows what they are just by looking at the Fill Factor and the "clean" "ever-increasing" ones get rebuilt at 99% so I can tell what they are just by looking at the Fill Factor. On the latter, even those eventually get a little fragmented just because not all inserts are in contiguous extents. They end up fragmenting about 1-2% in 4 years or so.
I can let you in on some of the other things I'm hammering out... After I evaluate indexes for 100% or 99%, then I do the following evaluations in the order listed.
- If a table has some pretty high logical fragmentation but has a page density near the max for the given row widths, it's because of a phenomena I call "Sequential Silos". I rebuild those at 98% because there's nothing you can do about it and to identify them as "Sequential Silos" just by checking the Fill Factor.
- For indexes that DO have ever increasing indexes that suffer "end of index" fragmentation because of an INSERT followed Expansive Update of the rows recently inserted, I rebuild/mark those with an 97% Fill Factor. The "7" is like a "footless" 2 , which means I have something "2 do" to the index to fix it but can't get to it right now.
- Anything with a random GUID or other seriously "random distribution", I do a couple of things... First, only use REBUILD on these. REORGANIZE does nothing to prevent fragmentation on these types of indexes and, in fact, makes the data stored on the pages much more prone to fragmentation. Depending on the insert rate and the kind of updates it may suffer, I'll set these indexes to 71, 81, or 91%. The "1" reminds me that it's incredibly important to REBUILD these indexes just as soon as they start to fragment because all of the pages fill at the same rate and they all get full at the same time. Once a couple of them start to fragment (anything over 1% according to my rules), they need to be REBUILT that night because the page splits and resulting fragmentation are at a tipping point and will soon "avalanche"... like the whole next day and the day after.These are the indexes where lowering the FILL FACTOR is guaranteed to make a good difference and, as you say, the space isn't wasted because the pages do fill up. I've actually done some testing that shows that it actually wastes a whole lot less space when you REBUILD rather than REORGANIZE these types of indexes and, like I said, REBUILD is much more effective at preventing fragmentation and can go weeks or even months with absolutely zero page splits... not even the supposed good ones. There are a lot of reasons to avoid GUIDs but, if you know the right way to maintain them, fragmentation isn't actually a reason to avoid them because they're actually one of the best in the world of fragmentation. They're actually what most people envision for indexes and the reason why people say that lowering the FILL FACTOR can reduce/prevent fragmentation even though it doesn't work well or at all on most other types of indexes.
Since I REBUILD these at 1% fragmentation, I call these "Low Threshold Rebuilds".
- There are a whole lot of indexes that suffer both logical fragmentation and low page density. This is usually from out of order inserts or out of order "Expansive" updates. The fix for those is to find out what the cause of the "Expansive" updates are and fix that problem. They're everywhere, though and it's not that easy (although I've built some tools to help) fix columns suffering from "Expansive" updates (Modified_By columns are a prime and constant culprit). They usually show up with a low page density and fairly high fragmentation (not usually as high as "Sequential Silos"). If such indexes get below a 70% page density, I'll rebuild them at 82%. The "2" in that is to remind me that I rebuilt the index only to recover space and still have something "2 do" to the index. If the index survives a couple of weeks without needing to be defragmented or recover disk space and I still don't have the time to find out why it fragments and try to fix it, I may set it to 92% or just leave it at 82%. Again, I never mix these up with "Sequential Silos".These 82/92 indexes, which are a good percentage of your indexes, are the ones that will cause major pages splits and a shedload of blocking. They must NEVER be rebuilt or reorganized at 0 or 100%.Most of these indexes fall into a category that I call "Random Silos" and can be caused by out-of-order inserts and "ExpAnsive" updates. Deletes also come into play here as well as the 97% Fill Factor category of indexes.
- On the subject of REORGANIZE... I never use it because it doesn't work like most people think it does and it's not the tame little kitty that the documentation makes it out to be. It's actually a resource hog, takes a whole lot longer to execute, and it's a log file exploder. So are ONLINE rebuilds and neither do as good a job at resolving fragmentation as a REBUILD, which are MUCH quicker. The only time to use REORGANIZE is to recover IN-ROW LOB space and I tend to force all LOBs out of row with a table option for doing so. MS killed us when the defaulted the new MAX and XML datatype to in-row instead of (out-of-row) like the old TEXT, NTEXT, and IMAGE datatypes were prior to 2005.
Another thing you mentioned and I'm curious what your take is; if you have a lower fill factor, say 70 percent like in your example, and that would yeild 42% wasted space. Wouldn't the open space be filled if it's a table with many inserts or updates (that happen to increase data in the middle)? There would be less page splits (so less fragmentation maybe?) and it could even be a great guess (after a lot of experimentation) to set them so the empty space gets utilized. So I'm wondering why you call it "wasted", couldn't it go to the right use under the right circumstances of course.
Again, "It Depends". It the graphic I posted, the index has a 70% Fill Factor and you can see the nice long Blue line of pages at the 70% page density level. You can also surmise by looking at the chart that it's an "Ever-Increasing" index that also suffers from "ExpAnsive" updates shortly after being inserted, which results in the ~50% page density of the pages further on the right.
So, based on that, you should also be able to surmise that the earlier pages in the index, which includes all those currently at 70%, will NEVER be updated and NEVER need room to grow because they're never modified and there are NEVER any out of order inserts. All the pages you see at the 70% line are stuck at 70% FOREVER. Following the numbered rules I outline above, this index should be rebuilt at 97% (which is the marker for "Ever-Increasing" and the recent inserts also have "ExpAnsive" updates) until you get around to somehow fixing the expansive updates... Then you could reclassify it as a 99% ("Ever-Increasing with NO "ExpAnsive" updates).
And I was wondering what you thought on fragmentation; There can be just physical fragmentation on the drive that needs to be defragged, and this should help performance(?) When I am thinking in terms of how a table is used, I'm thinking of that in the rhealm of logical fragmentation. So I'm think that running a defrag (without changeing the fill factor) would be a good thing on its own.
First of all, NEVER defrag an index with a 0 FILL FACTOR. Leave them alone unless you're going to change the FILL FACTOR according to the numbered rules I posted. Otherwise, you'll end up like I did... sucking my thumb and frantically twiddling my hair wondering where the hell all the blocking came from on the morning after index maintenance.
I would also never defrag an index that has a FILL FACTOR that ends in "5" or "0" regardless of the first digit because that means that someone (they always use "0" or "5" as the second digit) made the same (and sorry but) bad assumption that you just stated. That's another reason why my seemingly crazy number system has no (except for 100% static indexes) FILL FACTORs that end with "0" or "5".
As for fragmentation on the disk itself, I've tried and failed to convince any and all SAN administrators that it SOMETIMES makes a difference on spinning rust of all forms. In a lot of cases (actually, most cases), I believe (haven't had the privilege to test it) it will make very little difference because there are so many other people hitting the disk that the read heads are going to be jumping all over the place anyway. The one place where it might make a difference is on SANs that have large contiguous data workloads and very, very few concurrent users, like one or 2.
On SSDs, similar fragmentation simply isn't going to make a difference no matter what. Adding to that, they're supposedly better at using random data than sequential data but I've not tested that either.
You had mentioned a defrag venture cause blocking the next day, how did this happen?
That happened from me rebuilding indexes that had a "0" Fill Factor, which means I didn't know a thing about them. I was just like 99% of the rest of the world. When you rebuild an index that fragments a lot to 100% (which is what a Fill Factor of "0" really is), you slam everything into the proverbial ceiling with no "head room". If you do an out of order INSERT or an "ExpAnsive" update (even just 1 additional byte can do it), the pages are virtually guaranteed to split, which puts a system transaction across at least 3 pages... the target page, the next logical page, and the new page for the split. All of that and the reassignment of previous and next page pointers in the headers of all 3 pages and all of the data movement of moving roughly half the rows to the new page, plus the insert or update, are all fully logged regardless of recovery model and there are frequently more than 1 log entry per for moved. While all that is happening, all 3 of the pages are locked and blocked.
If you do that to about half of all the indexes in your database during index maintenance, then all those page splits on all those indexes happen on your most critical tables/indexes the very next day and that causes MASSIVE blocking.
is pronounced "ree-bar
" and is a "Modenism
" for R
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!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)