Developers can identify, in Microsoft SQL Server databases, a fill factor to specify how full each index page can be. The fill factor is the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of free space for future growth. For example, specifying a fill factor value of 70 means that 30 percent of each leaf-level page will be left empty providing space for index expansion as data is added to the underlying table. 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. Developers specify a fill factor when they want to fine-tune performance, for example when they are creating a new index on a table with existing data, and particularly when they can accurately predict future changes in that data. When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to specify a fill factor for an index, they should follow the next steps:
1. In Object Explorer, right-click the table with an index for which they want to specify a fill factor and click Design (Modify in SP1 or earlier).
The table opens in Table Designer.
2. From the Table Designer menu, 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. 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.