SQL Fill Factor and Performance

In SQL when a index is created or rebuilt, the fill factor value determines the percentage of space on each leaf-level page to be filled with data. By setting the fill factor you can control the amount of space initially allocated to table ‘s data page. Fill factor option is provided for fine tuning index and data storage and performance. 

Index Fill Factor

The smallest unit in SQL server is a page which is made of page with size 8K.  the Fill Factor indicates the percentage value to be filled on data page with data in SQL server. Means it determines the percentage of space on each-leaf level page to be filled with data.It plays a vital role in Query Performance Tuning.

Default value of Fill Factor : 0  or 100

Value set to 100, indicates 100% space utilisation for data storage. Value set to 90 will allow 90% storage occupancy of data page and rest of the space will be free. This free space we be taken into consideration when data is being updated or modify and is not used for new records insertion. 

But you might be thinking why are we not simply setting the value to 100 and utilises the complete space? Why we need to leave free space on each page?  

Lets try to answer this question with example:

Say We have a pages  completely filled  with data and now we want to add new records then the a completely filled page will now  be split into  two half filled page. This page splitting process is expensive. And also now system has two read two pages means higher usage of memory, CPU and IO bandwidth.

To set the fill factor for an index, we can use the following code:

CREATE INDEX [index_name] ON [table_name] ([column_name])
WITH (FILLFACTOR = [fill_factor_value]

Query:

CREATE INDEX my_index ON my_table (my_column)
WITH (FILLFACTOR = 80)

We can also modify the fill factor of an existing index with the following code:

ALTER INDEX [index_name] ON [table_name] REBUILD WITH (FILLFACTOR = [fill_factor_value])

Query:

ALTER INDEX my_index ON my_table REBUILD WITH (FILLFACTOR = 80)

Demonstration of Fill Factor with  Page Splitting 

As you can see with higher fill factor, a new insert causes a page to split which can leads to fragmentation. with a low fill factor there is space left at each page which reduce the need for page splits and fragmentation.

-- Create a table
CREATE TABLE MyTable (
   ID INT PRIMARY KEY,
   Name VARCHAR(50)
)
-- Insert some data
INSERT INTO MyTable (ID, Name)
VALUES (1, 'John'), (2, 'Mary'), (3, 'Bob'), (4, 'Alice'), (5, 'Tom')

-- Create an index with high fill factor (100%)
CREATE INDEX MyIndex_HighFillFactor ON MyTable (ID) WITH (FILLFACTOR=100)

-- Create an index with low fill factor (50%)
CREATE INDEX MyIndex_LowFillFactor ON MyTable (ID) WITH (FILLFACTOR=50)

-- Query the table using the high fill factor index
SELECT * FROM MyTable WHERE ID = 3

-- Query the table using the low fill factor index
SELECT * FROM MyTable WHERE ID = 3

By measuring the execution time of two queries you can see the impact of fill factor on query performance. 

How To Decide the Best Value for a Fill Factor?  

There are many  factor to be considered while deciding the fill factor some are:

• Rate of data Growth
• Size and usage of table
• Frequency of data update

1. Data growth rate : Considered if the table is expected to grow quickly to ensure not to rebuilt the index frequently you might like to keep lower fill factor value.

2. Frequency of Data Update: If you are required to update your data frequently then to allow more free space lower fill factor will be a better choice as it will reduce the page spilt. 

3. Table size :  If the size of table is small then to avoid waste of wastage of space you higher fill factor is preferred and vice versa.

4. Monitor Index fragmentation: Regardless of fill factor it’s important to maintain the index fragmentation regularly and rebuilt the index to enhance the performance. 

Note: An Index fragmentation is a situation which occur when data pages in index are logically distorted, loosely filled or overfilled.

Fill Factor of a Table

Query:

Table Name : data

SELECT
   name AS index_name,
   (avg_page_space_used_in_percent/100) AS fill_factor
FROM
   sys.dm_db_index_physical_stats (DB_ID(), 
   OBJECT_ID('data'), NULL, NULL, 'DETAILED')
   JOIN sys.indexes ON indexes.object_id = 
   OBJECT_ID('data') AND indexes.index_id = index_id
WHERE
   index_level = 0;
 

Output:

index_name  fill_factor
---------- -----------
PK_orders   0.75

Conclusion

Deciding the correct and the required fill factor will cause reduce in page splitting and reduce overall cost and will also lower the use of memory and CPU and ultimately increasing the performance.