Microsoft® SQL Server 2012 Unleashed

Setting the Fill Factor

Fill factor is a setting you can use when creating an index to specify, as a percentage, how full you want your data pages or leaf-level index pages to be when the index is created. A lower fill factor has the effect of spreading the data and leaf index rows across a greater number of pages by leaving more free space in the pages. This reduces page splitting and dynamic reorganization of index and data pages, which can improve performance in environments where there are a lot of inserts and updates to the data, while at the same time reducing performance for queries because an increased number of pages need to be read to retrieve multiple rows. A higher fill factor has the effect of packing more data and index rows per ...