What is Fill Factor ?

                                                       Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100. 

                                                    If the page is completely filled and new data is inserted in the table which belongs to completely filled page, the “page split” event happens to accommodate new data. When new data arrives, SQL Server has to accommodate the new data, and if it belongs to the page which is completely filled, SQL Server splits the page in two pages dividing the data in half. This means a completely filled page is now two half-filled pages. Once this page split process is over, the new data is inserted at its logical place. This page split process is expensive in terms of the resources. As there is a lock on the page for a brief period as well, more storage space is used to accommodate small amounts of data. The argument usually is that it does not matter as storage is cheaper now a day. Let us talk about Fill Factor in terms of IO in the next paragraph.

                                  

                It is absolutely true that storage is getting cheaper every day.      .Now let us assume that there is Table 1 which contains the data worth of 1000 pages. All these pages are 100% filled. If we run a query to retrieve all the data from the SQL Server, it should retrieve all the 1000 pages. If pages are only 50% filled to accommodate Table 1, we will need 2000 pages, which means SQL Server has to read twice the amount of the data from the disk, leading to higher usage of memory, CPU and IO bandwidth.

                                Reading previous paragraph gives us an impression that having Fill Factor 100 is the best option as there won’t be any empty space in the page and IO will be always optimal. Again, this is true if we never do any insert, update or delete in the table. If we insert new data, ‘Page Split’ will happen and there will be few pages which will have 50% Fill Factor now. An OLTP system which is continuously modified ‑ this has always been a challenge with regard to having the right Fill Factor. Additionally, note that Fill Factor only applies to a scenario when the index was originally created or index was rebuilt; it does not apply subsequently when page split happens. As discussed earlier, page split increases IO as well storage space.

                         So,higher Fill Factor and high transaction server implies higher page split. However, Fill Factor can help us to reduce the number of the page splits as the new data will be accommodated in the page right away without much difficulty and further page splits. We can measure the page split by the watching performance monitor counter “SQLServer:AccessMethods:Page Splits/Sec”.

 Additionally, you can measure the same using the following sys query.

SELECT cntr_value
FROM MASTER.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'

Fill factor is usually measured at the server level as well as table level. Below we have the scripts for the same.

Here is the script to measure the Fill Factor at the server level:

SELECT *
FROM sys.configurations
WHERE name ='fill factor (%)'

And, here is the script to measure the Fill Factor at the table/index level:

USE YourDatabaseName;
SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor
FROM sys.indexes