May 23, 2004 at 8:08 pm
The fill factor option on indexes is confusing to me. It appears I only know of two options for the fill factor. The default rule is don't change it and it gets it's value from the server which is 0 by default. If the data is read only then set it to 100. This saves the most space.
Ok, let's say you can predict how the data will grow. What should you set it at? Let's say you know the data will grow 10% / year. What do you set the fill factor? Is there a formula that is widely accepted? Has someone written an article on this subject? BOL really doesn't have a lot to say on the subject.
Thank you in advance.
Curtis
May 23, 2004 at 8:42 pm
May 24, 2004 at 8:55 am
There's a few articles on this site regarding fillfactor and how it works. Basically if you're data is static, not many inserts/updates/deletes, choose a high fillfactor, like 80-90%. If you have lots of changes, then perhaps a lower, 50-60%, but you really need to monitor somehow the changes. Auditing is the only real way to do this. Profiler, home grown (ApexSQL has a product), Lumigent's Log Explorer, etc.
http://www.sqlservercentral.com/columnists/gjackson/whocaresaboutfillfactor.asp?Tab=1
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
May 25, 2004 at 6:49 am
I would also think you would want to take into account the frequency of maintenance on these indexes. If you are rebuilding the indexes weekly, you could go with a higher fill-factor because they will be rebuilt. If you are just updating statistics, then you may want the fill-factor lower.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply