Fill factor for index

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.

/Argyle

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
Webmaster
SQL-Server-Performance.Com