Index FILLFACTOR

Includes the "FILLFACTOR <n>" clause in the create index statement during index creation. Default fillfactor of SQL Server is 0, which indicates a dynamic approach. Although a low fillfactor value other than 0 can reduce the requirement to split pages as the index grows, the index requires more storage space and can decrease read performance. Even for a table that is 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 fillfactor other than the default can decrease database read performance by an amount inversely proportional to the fillfactor setting. For example, a fillfactor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, thereby increasing the disk I/O operations required to retrieve the data.

Using the fillfactor setting gives the most benefit when the number of rows in the table does not change (the table is static), but there is a significant amount of update activity on existing rows.

The First Free Numbers (tcmcs050) table is an example of such a table. The example shows an msql_storage_param (Infor Baan 5.0/LN) file entry with the fillfactor set to 1 for table tcmcs050.

tcmcs050:*:T:FILLFACTOR 1

A low fillfactor setting also helps reduce page and index splitting by allocating a greater number of pages for the data and indexes with ample extra space to accommodate new rows.