Thomas Franz wrote:
Stupid question, but why are some people use a low fill factor as 70 or 80%, I'm usually fine with something as 95-98 (or even 100 if it is on a identiy column and has no varchar() columns that WILL (not could) be updated by a longer value).
I understand, that there may be exception, as when you use an UNID as primary key (maybe because you are using some sort of replication / synchronisation over several sides), but in this case I'd use the UNID-column (usually) only as nonclustered index.
Usually > 90 % of your database are old data that will almost never be updated. When your main key is ascending (dates, own or "foreign" (as the order_id in the order_position table) identity columns etc.) you will never insert in the middle of the index. And even if you have an index on the StackOverflows Posts table on the user_id or cluster your order table (for whatever reasons) by the customer_id it will be usually only one or two inserts per day / week / whatever your index optimize window is, so 95% gives you usually more then enough space for those inserts.
The subject of what Fill Factor to use is a huge one.
If, for example, you have an ever-increasing index that you do inserts to and follow that (before the next index maintenance) with "ExpAnsive" Updates, using a lower Fill Factor to try to avoid page splits is a totally futile effort because the new inserts go in at 100% no matter what the Fill Factor is. And, as you point out and especially for ever-increasing indexes, it's actually an ignorant waste to have a Fill Factor on a table where only the latest "tip" of the index is active.
If, on the other hand (for example), you have a Random GUID keyed index (or any other index that suffers fairly evenly distributed inserts or "ExpAnsive" updates), then a lower Fill Factor (like 70%) can prevent page splits for millions of inserts or updates for months with no index maintenance. Row size also plays an important consideration at to what the Fill Factor should be, as well.
Of course, there are some crazy circumstances where your index massively fragments but with absolutely no page splits. No... that's not impossible. I see it happen a whole lot. That also means no page density (physical fragmentation) problems. Assigning a low Fill Factor to those is also an ignorant waste. I have a "numbering system" for Fill Factors to identify what type insert/update/frag pattern is exhibited by an index. It call the ones I'm talking about "Type 98". The 8 is like an infinity symbol stood on end and is what I use to mark indexes that are "fragmented forever" and aren't an "Even Increasing Index that could actually be repaired (which is a "Type 97" to me)
You say you're usually fine with a 95-98% Fill Factor but under what conditions are those "fine"?
And I'm not sure what you mean by an "UNID". Do you mean a UUID (also known as a GUID in SQL Server)?