FILLFACTOR specifies the percentage for how full the Database Engine should make the leaf level of each index page during index creation or rebuild. FILLFACTOR must be an integer value from 1 to 100. The default is 0.
If FILLFACTOR is 100 or 0 (MS SQL treats these the same), the Database Engine creates indexes with leaf pages filled to capacity.
The number you set only applies when the index is created or rebuilt…otherwise, the Database Engine will continue to fill in spaces in the index left available during the last create or rebuild statement. If you need to see the current FILLFACTOR setting for an index, check out sys.indexes.
The reason you have the FILLFACTOR option is to fine tune storage and performance in your database. If you set the FILLFACTOR low (below 50), then you’ve optimized the index for inserts, but you rarely need to do that. When you set the FILLFACTOR high (above 50) you’re optimizing for selects and searches.
The reason this is so is due to page splits. Let’s say you set the fill factor to 0 (or 100). When you insert a new record, the data page (basic unit of storage in the database) must be split. Half the rows to a new page to make room for the new row. This page split makes room for new records, but takes time to perform.
Page splits also cause fragmentation. That causes increased I/O operations. This too can slow down reads across the long haul. If you’re getting frequent page splits, rebuild the index. This is particularly useful after importing a large file into the database. A large file would be something that increases the table’s size by a noticeable percentage (my threshold is usually 25% growth in a table under a gig, and 10% growth for tables over a gig). I’m kind of paranoid about performance that way.
Now, when you choose a low FILLFACTOR below 50%, but non-zero, you may have fewer page splits, but the amount of space used to store the data will be higher. Most of the time reads are more important than writes or updates by a factor of 5 to 10 (per Microsoft’s Books On Line) so optimizing for inserts or updates is a losing cause.
I would like to point out one final note from Microsoft: “specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.”
This is something you’ll have to consider if you have users complaining about slow access times, even when your tables and views are highly indexed. It’s all a balancing act. And you are the clown that has to learn to juggle while standing on a large inflated ball! Need help? Get in touch with me, I can help! I do offer consultation services. Email me today for more information.