Fill Factor
When you create a clustered index, the data in the table is stored in the data pages of the database according 
to the order of the values in the indexed columns. When new rows of data are inserted into the table or 
the values in the indexed columns are changed, Microsoft® SQL Server™  2000  may have to reorganize the storage 
of the data in the table to make room for the new row and maintain the ordered storage of the data. 
This also applies to nonclustered indexes. When data is added or changed, SQL Server may have to reorganize 
the storage of the data in the nonclustered index pages. When a new row is added to a full index page, 
SQL Server moves approximately half the rows to a new page to make room for the new row. 
This reorganization is known as a page split. Page splitting can impair performance and fragment the storage 
of the data in a table. For more information, see Table and Index Architecture.

When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space 
on each leaf level page of the index to accommodate future expansion in the storage of the table's data 
and reduce the potential for page splits. The fill factor value is a percentage from  0  to  100  that specifies how 
much to fill the data pages after the index is created. 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. This setting is more appropriate when t
here will be changes to the data in the table.

The fill factor option is provided for fine-tuning performance. However, the server-wide default fill factor, specified 
using the sp_configure system stored procedure, is the best choice in the majority of situations. 



Note  Even for an application oriented for many insert and update operations, the number of database reads
 typically outnumber database writes by a factor of  5  to  10 . Therefore, specifying a fill factor other than 
the default can degrade database read performance by an amount inversely proportional to the fill factor setting. 
For example, a fill factor value of  50  percent can cause database read performance to degrade by two times.


It is useful to set the fill factor option to another value only when a new index is created on a table with 
existing data, and then only when future changes in that data can be accurately predicted.

The fill factor is implemented only when the index is created; it is not maintained after the index is created as data 
is added, deleted, or updated in the table. Trying to maintain the extra space on the data pages would defeat 
the purpose of originally using the fill factor because SQL Server would have to perform page splits to maintain 
the percentage of free space, specified by the fill factor, on each page as data is entered. Therefore, if the data 
in the table is significantly modified and new data added, the empty space in the data pages can fill. 
In this situation, the index can be re-created and the fill factor specified again to redistribute the data.