:::: MENU ::::

Using the Index Fill Factor in SQL Server

  • Nov 05 / 2008
  • 0
dbDigger, Indexes, Performance Tunning and Optimization

Using the Index Fill Factor in SQL Server

If you know that your index will have constant updates or insertions, you can reduce the number of page splits that occur by leaving some extra space in the leaf pages of the index. This is done via the index fill factor. The fill factor specifies how full the index should be when it is created.
By specifying the fill factor, additional rows are left empty in the index pages, thus leaving room for new rows to be added. The fill factor specifies the percentage of the index to fill and can take an integer value from 1 to 100. Setting the fill factor to 75 fills the index 75 percent, leaving 25 percent of the rows available.
The disadvantage of setting the fill factor is that the indexes will be larger and less efficient than a tightly packed index.
The advantage of setting the fill factor is that page splits can be reduced. The fill factor setting appropriate for you really depends on your configuration and the load on the system. Remember that indexes, like all data, are stored both on disk and in memory in 8K pages. Setting the fill factor of an index to 50 causes the index to take up twice as many pages as a fill factor of 100. Keep this in mind when setting the fill factor.

Consult us to explore the Databases. Contact us