Index Fill Factor
FILLFACTOR, The FILLFACTOR option allows you to allocate a percentage (0 to 100) of free space on the leaf-level index pages to reduce page splitting. This option is many-a-times overlooked and just left set to the default engine setting without knowing the fact that, this could lead to serious performance issues.
As stated earlier also, the availability of free space in an index page can have a significant effect on the Performance of index updates operations. If an index record must be inserted and there is no free space, a new index page must be created and the contents of the old page split across the two pages. This can affect performance if it happens too frequently. SQL Server offers two important options that give you control over the amount of free space maintained within an index: FILLFACTOR and PAD_INDEX.
The Fill Factor specifies the % of fullness of the leaf level pages of an index. When an index is created or rebuilt the leaf level pages are written to the level where the pages are filled up to the threshold specified and the remainder of the page is left blank for future usage. This is the case when a value other than 0 or 100 is specified. For example, if a fill factor value of 60 is chosen, the index pages are all written with the pages being 60 % full, leaving 40 % of space for future usage. By default, the Index fill factor is retrieved from the Instance Database settings as displayed below:
The PAD_INDEX Option
The PAD_INDEX option enables you to specify whether or not the fill factor applied to the leaf pages also applies to the non-leaf pages. You can use the PAD_INDEX option only when FILLFACTOR is specified, because the PAD_INDEX percentage value is determined by the percentage value specified for FILLFACTOR.
By default, SQL Server always leaves enough room to accommodate at least one row of the maximum index size for each non-leaf-level page, regardless of the fill factor value.
The sysindexes system table stores the fill factor value that was last applied to an index, along with other index information. You can change the default fill factor value at the server level by either using the sp_configure system stored procedure or using SQL Server Management Studio.
Setting and finding the ideal Fill Factor for your database is not so easy and straightforward. There is no magic number to this; we need to consider multiple facts before changing this. A high Fill Factor will ensure more rows are packed on each data page but there is a big chance that page splits will increase, especially in OLTP systems. This is something that is not desirable since the less page splits the better for performance. On the other hand, a low Fill Factor will store less rows per data page, which will decrease page splits but will require more resources, such as IO, to read the same amount of data since the data is spread across more data pages.
Apart from setting a Fill Factor for the whole database, we can also override this setting for individual index also while we are Rebuilding, Reorganizing or creating an index.
Guidelines for Setting the FILLFACTOR Option
The fill factor value that you specify on a table depends on how often data is modified (INSERT and UPDATE statements) and your organization’s environment. Generally, you should:
• Use a low fill factor value for online transaction processing (OLTP) environments. This provides maximum room for growth in tables where rows are inserted frequently or index key values are frequently modified.
• Use a high fill factor value for online analytical processing (OLAP) environments.
Now let’s validate some facts:
I’ve created two tables named “Table_FF_0” “Table_FF_50” and inserted 100000 records in both the tables, after that I created one clustered index on each table. While creating first clustered index on Table “Table_FF_0” I used Fill Factor 100% and on second table ““Table_FF_50” I used Fill Factor 50%
Now when you check the space used for each table you’ll notice that the index size of Second table is almost Double than the First table, which is because SQL Server kept half the pages blank in order to accommodate later entries, if any and that is why SQL Server reserved double pages to store the same number of records. (Proves Fill Factor works)
Index Size of both the tables
Scenario 2 - Select Performance
Now lets analyse the SELECT performance for both tables/indexes. The SET STATISTICS IO option is enabled which will return IO information, needed to analyse the performance difference.
When you’ll try to read some data from both the tables SQL Server will have to read more Pages in order to get the same rowset. This can be proved with the help of Statistics IO, Just enable Statistics IO and execute Select command over both the tables you’ll get the IO Info along with the output.
You can refer the below screenshot which clearly shows that SQL Engine reads 45 pages from First Table where the pages where 100% Full whereas in the second table SQL Engine ends up reading 86 Pages to get the same data because the data pages were half blank.
Execute DBCC ShowContig Command against both the tables and you can verify both the above mentioned points from the output.
Scenario 4 – Page Splitting
You can also test whether Page Splitting happens with high Fill Factor or not.
The only trick we can play here is: - took one INT column in each table and insert EVEN numbers in both the tables and after creating Indexes on this column we’ll insert ODD Numbers. This way SQL Server will be forced to Split the pages in the first Index because there is no Room Space left for later insertions and you can see that with the help of below screenshot. This is evident.
Here you can see that the Logical Fragmentation of the First Table has increased to 66%.
When you actually look into the scan density count you’ll notice that the Actual Count is many folds greater than the Best Count because of the Page Splitting.
So in Nutshell, it is clear that a high Fill Factor will improve SELECT but will penalize DML due to more page splits, while a low Fill Factor will improve DML statements but will penalize SELECT statements.
After going through this Doc, DO NOT run to your production server and quickly modify the Fill Factor of your indexes of your database. However, from the tests performed, it's clear that by carefully monitoring the Index fragmentation of heavily updated tables, the Fill Factor option will help in reducing page splits and index fragmentation, thus speeding up DML statements. The best value is the one which will find the right balance between SELECTS and DML operations.
Thanks & Regards,
Sarabpreet Singh (VP SQLServerGeeks.com)