Discussion in 'T-SQL Performance Tuning for Developers' started by cb, Feb 10, 2003.
What is the best value for fill factor on indexes?
Depends on the activity on your table. If it's mostly read you want a high fill factor to avoid havnig the data on many pages. If it's a lot of writes you want a lower fill factor to allow for inserts without getting too many page splits.
As a rule of thumb, I start with a fillfactor of about 90, and then test from there. After going with a fillfactor of 90, I then watch the number of page splits with Performance Monitor. If page splits are few, then I will raise the fillfactor to 95, and then watch page splits. If they are still few, then I leave it at 95, but if they are not higher, then I move back to 95. If I start at 90, and find that page spits are excessive, then I lower the fillfactor about 5% each time until page splits go away. As Arglye as said, the exact amount depends on how write intensive your database is.
Brad M. McGehee
Separate names with a comma.