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™ 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 adding a new row to a full index page, SQL Server will move approximately half the rows to a new page to make room for the new row, a reorganization known as a page split. This can impair performance and fragment the storage of the data in a table.
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 there 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 a heavy INSERT/UPDATE oriented application, 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.
| Creating an Index | fill factor Option |