SQL Fill Factor and Performance
Last Updated :
01 May, 2023
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.Â
Share your thoughts in the comments
Please Login to comment...