How to: Specify Fill Factors for Indexes
In Microsoft SQL Server databases, you can identify a fill factor to specify how full each index page can be. The fill factor is the percentage of free space allotted when a new index page is created. 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.
Specify a fill factor when you want to fine-tune 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.
Note
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.
To specify a fill factor for an index
In Server Explorer, right-click the table with an index for which you want to specify a fill factor and click Open Table Definition.
The table opens in Table Designer.
From the Table Designer menu, click Indexes/Keys.
The Indexes/Keys dialog box opens.
Select the index in the Selected Primary/Unique Key or Index list.
In the Fill Factor box, type a number from 0 to 100.
Note
A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space.