Facts About the Fill Factor
I selected a small table to demonstrate the effects of changing the fill factor on an index and to show how these changes affect how full the pages used by the index are. Fill factor sets the amount of room left empty in an extent so that new entries can be moved into the extent while still preserving the order of the index. Bear in mind that if you are using an identity on a clustered index, then the default fill factor should be fine because intervening values wont be inserted into the index. Each new entry will go to the end of the page because it will be the next highest number in the sequence. If you create clustered keys on identity columns in a table that has heavy transactional activity, you could run into some limitations. By creating a clustered index on an identity, youll create a "hot spot" on the last page of the index. New entries follow one another because of the serial nature of the key, and they end up competing for the last open space on the page. In a table thats heavily transaction-oriented, this could create a bottleneck.

The whole point of the fill factor is to reserve space on an index page to avoid page splitting, a very inefficient operation. A page split is the internal mechanism the server uses to make room for a new element in a file. If an index page splits, the system consumes more disk I/O to read the index allocation map as it tries to locate the next segment on a disk. New pages are not created contiguously, so fill factors leave a little room to grow and hopefully minimize—or at least postpone—page splitting.

A table that wont experience any insertions in its data rows could have a fill factor of 100, meaning that the pages will be filled to capacity (100 percent) in the index order. Without reserving a little headroom on the index page, any change in the constitution of the file will result in a page split. On the other hand, a file that is subject to frequent additions and deletions could be left with a fill factor of 10. Ninety percent of the space on a page would be left vacant, allowing new entries to be added with less rearrangement of the index.

Evaluating an Index
The DBCC DBREINDEX command enables you to change the fill factor of an existing index, and the DBCC SHOWCONTIG command provides the information you need to evaluate the operation by telling you how efficiently the index is stored. If your system is too busy to tolerate the downtime of DBREINDEX, DBCC DBDEFRAG is an online operation that can proceed while users are in the database.

Start the process of evaluating an index by looking at the output DBCC SHOWCONTIG produces. This process can report all tables and indexes in the database or just the ones you specify. You can then take cues from that data. Because each tables profile of use is different, the fill factor used for each one should be evaluated in terms of the frequency of its update and deletion activity. Some tables, like look-up tables, will be static and wont need extra room to avoid page splitting. Casting these tables with a high fill factor makes sense.

On the other hand, tables that are subject to lots of processing (i.e., insertions and deletions) would benefit from a lower fill factor, which reserves room on their pages. This way, DBREINDEX or DBDEFRAG can shuttle old values about to keep the index internals in order. Figure 2 shows the results of the first pass of SHOWCONTIG with the fill factor set to 90. It is a small table, so the results are not dramatic, but they are informative.

Figure 2: The Results of SHOWCONTIG with the Fill Factor Set to 90

The data demonstrate that the index consumes two pages on two extents (small tables are first stored on mixed extents, subsequent additions are then stored on their own extent) and that the table occupies a little more than three quarters of each page on which it exists. I can influence the layout of the index by using the DBCC DBREINDEX command with a different fill factor (see Figure 3), hypothesizing that this will be an active table that needs room to accommodate new entries.

Figure 3: The DBCC DBREINDEX Command

A subsequent running of DBCC SHOWCONTIG shows the effect of the new fill factor, 50 versus 90. The new fill factor leaves more room on each page but its side effect is that the index now takes up more pages (see Figure 4), a logical consequence of leaving more room per page.

Figure 4: The Results of SHOWCONTIG with the Fill Factor Set to 50