Whenever you create a new index in SQL Server or rebuild an existing index, you can specify several optional parameters – one of such parameters is the fill factor. This parameter controls the percentage of free space in the leaf level of the index pages are filled when they are created. So, a fill factor value of 100% or 0% means that each index page is 100% full, a fill factor of 90% means that each index page is 90% full and 10% of each leaf level page will be empty thus providing space for future index expansion as DML statements occur. The default is 0% which is the same as 100%.
If in your environment, you have tables that have a lot more deletes+inserts+updates done on them, you should consider choosing a non-default value for the fill factor. The reason is that if the default value is used and a new row needs to be added to the page, the engine will have to do a page split and will move half of the rows from that page to the new page in order to make room for this record. Page splits are a resource intensive operation and can cause performance issues in a highly transactional system as well as in data marts. It can also cause increased fragmentation and thus increase the I/O.
Suppose you added an index on a table with the default fill factor. When the index gets created, the engine will place the index on contiguous physical pages thus allowing for optimal I/O since the data can be read sequentially instead of randomly. As the DML operations start happening on this table, due to page splits, the engine will now need to allocate new pages elsewhere on the disk and these will not be contiguous thus random I/Os will be used instead of sequential and that will be a costly operation.
You can ascertain the right fill factor value for your environment by benchmarking and playing with different values. However, you can use this as a rule of thumb for choosing the fill factor value in your environment:
1) For low DML operation tables – tables that are mostly used for reads only: Keep the default fill factor of 100% or 0%.
2) For tables that have a lot of DML operations against them (many more writes than reads), go with a value of 80%.
3) For tables that fall in between or are seasonal tables (i.e. they get heavily utilized only in certain times of the year), go with a value of 90%.
Choose carefully and judiciously – because if you choose too low of a fill factor value, the page splits will be reduced – however, it will also increase the number of pages that SQL Server then has to read for the queries and that will have an impact on performance. This will also have an impact on your data buffer as those pages move from disk to the buffer cache and those pages with their empty spaces will occupy the buffer which means that you can fit in less number of pages at the same time in the buffer which can impact performance.