SQL Server table indexes have a kind of voodoo to them that can be alienating. The bigger the table and the more commonly it’s accessed, the greater the need for a properly tuned index.
The fill-factor option for a SQL Server table index is one of the most common tuning methods to apply to an index. A fill factor for an index is a percentage that tells SQL Server how much of each leaf-level page in the index should be filled with index data, and how much should be left as room to grow into. Such expansion happens if columns in the underlying table are changed or added, or when rows are added to the table. If the index fragments over time, or if it exhibits poor performance, that’s a sign you may need to tweak the fill factor for the indexes on your most commonly used tables. The question is how.
The first thing you must do is avoid, as has many have discussed (e.g.,
The second thing to bear in mind: There may be many instances in which you don’t need to adjust the fill factor much, if at all. Don’t bother with indexes that only have a few pages (as per the DBCC SHOWCONTIG command), since those tend to be cached heavily, anyway. Go first for the indexes that have the largest number of pages and have low scan densities (again, as per the DCC SHOWCONTIGcommand). An index with only 12 pages and a density of 50% is going to be far less of a problem than an index with 7,000 pages and the same density.
Third, figure out how much tuning you need and in what direction you should go, since there’s no one magical value. Set the fill factor too high and you’ll see a great deal of wasted space and page splits across the board. Set fill factor too low, and your database size will increase, which in turn impacts performance all over again because of the amount of I/O that needs to be performed.
Randal suggests starting with 70%, using that as the default value for a week or so, and then tuning that number up or down to see what changes result. The Page Splits/Secperformance counter for SQL Server is a big help in this regard; the higher it is, the more tuning your SQL Server table indexes probably need.