Thanks for the fine article. It raises a few questions for me.
- How is the optimizer affected by FF?
- If a FF of 50 is used, causing twice as many reads as a FF of 100, then could it cause the optimizer to not use an index?
- If, in this same scenario, the leaves eventually get more filled will the optimizer start using the index because less reads are required?
- Since I operate in a 24/7 environment, I have to try to minimize the amount of index rebuilding I do, since it locks the table for the duration of the rebuild and causes blocking for my users. I have a few fairly large tables (~10,000,000 rows, ~600,000 pages) that are heavily used for both reads/selects and DML. To minimize index rebuilding, would it make sense to go with a lower fill factor, knowing that would penalize the selects? In other words, does a lower fill factor keep an index from getting fragmented as quickly?