• Article

How to: Specify Fill Factors for Indexes (Visual Database Tools)

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.

To specify a fill factor for an index

  1. In Object Explorer, right-click the table with an index for which you want to specify a fill factor and click Design.

    The table opens in Table Designer.

  2. On the toolbar, click Table Designer and then click Indexes/Keys.

    The Indexes/Keys dialog box opens.

  3. Select the index in the Selected Primary/Unique Key or Index list.

  4. 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.