Specifying a Fill Factor for an Index

In Microsoft^® SQL Server™ databases, you can identify a fill factor to specify how full each index page can be. The amount of empty space on an index page is important because when an index page fills up, the system must take time to split it to make room for new rows.

It is seldom necessary to specify a fill factor when you create an index. The option is provided for fine-tuning performance. It is useful when you are creating a new index on a table with existing data, and particularly when you can accurately predict future changes in that data.

To specify a fill factor for an index

1. In your database diagram, select the table for which you want to specify a fill factor.
   
   
2. Right-click the table, and choose Properties from the shortcut menu.
   
   
3. Choose the Indexes/Keys tab.
   
   
4. Select the index from the Selected index list.
   
   
5. In the Fill factor box, type a percentage from 0 to 100.

By default, the fill factor is set to zero. If you change a fill factor after the index has been created, the database does not dynamically update the specified percentage of empty space in the pages. The value of the original fill factor is stored with the index in the database catalog.

For more information about fill factors and their uses, see "sp_configure System Stored Procedure" in SQL Server Books Online.