Fill Factor in SQL Server: Optimizing Index Performance

 When it comes to optimizing performance in SQL Server, one factor to consider is the fill factor. The fill factor determines the percentage of space on each leaf-level page that is filled with data, leaving room for future growth. By setting an appropriate fill factor, you can reduce fragmentation and improve index performance.

What is Fill Factor?

In SQL Server, indexes are stored as B-trees, which consist of multiple levels of pages. The leaf-level pages hold the actual index data. The fill factor determines how much space on these pages should be filled with data. For example, a fill factor of 80 means that each leaf-level page should be filled to 80% of its capacity, leaving 20% empty for future data.

Why is Fill Factor Important?

Setting an appropriate fill factor is crucial for optimizing index performance. When data is inserted or updated in a table with an index, SQL Server needs to find space on the appropriate leaf-level page to accommodate the new data. If the page is already full, SQL Server needs to split the page and allocate a new page, causing fragmentation. This fragmentation can lead to decreased read and write performance.

Determining the Right Fill Factor

The ideal fill factor depends on the characteristics of your data and workload. If your data is constantly changing with frequent insertions and updates, a lower fill factor (e.g., 70 or 80) can help minimize fragmentation. On the other hand, if your data is relatively static, a higher fill factor (e.g., 90 or 100) can maximize storage efficiency.

 

In our database, we may have some meta data tables which would not be changed by application those can be configured as 0 or 100 value. 

Some of the tables are modifying rarely and continuously used in select query then we can choose the value 75 to 90 based on the data feed on the table. The tables are frequently used in data modification and select statement and the ratio is 50-50 then we can choose the fill factor value as 50. If the tables are rarely used in select query and frequently modifying by application then we can choose the fill factor value 35 to 50 based on the data feed on the table.

Let's assume we have a table called TABLE_A which contains 500 pages of data and each page filled 100% of data by using fill factor value 100. When we are retrieving whole data from the table, the SQL engine reads 500 pages to bring the data. If we choose the fill factor value 50 on same table the number of pages will be allocated as 1000 and the SQL engine reads 1000 pages to bring the same data. The SQL engine puts double efforts to bring data from the table and it is leading to higher usage of memory, CPU usage. So we need to choose appropriate fill factor value on the table.

Implementing Fill Factor in Index

To set the fill factor for an index in SQL Server, you can use the FILLFACTOR option when creating or rebuilding the index. For example, to create an index with a fill factor of 80, you can use the following syntax:

CREATE INDEX [IX_MyTable_Column] ON [dbo].[MyTable]([Column]) WITH (FILLFACTOR = 80)

Additionally, you can modify the fill factor for an existing index using the ALTER INDEX statement.

Benefits of Fill Factor

Fill factor helps to avoid the page split when data modification happens on the table. For instance we have a table which filled 100% of data on each page. If we modify the existing row on the table which is not allowed to stored on same page due to insufficient space, the row will be moved to another page. This is called page split and its leading the memory and performance issue in SQL Server.

How to Change Fill Factor Value in SQL Server?

We can change the fill factor value in server level which will be implemented whenever the index are created. This is can be configured by using below mentioned query and also we can change it 

manually.

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

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

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90 -- Fill Factor Value
GO
RECONFIGURE
GO

Conclusion

Optimizing index performance is crucial for maintaining a responsive and efficient SQL Server database. By understanding and setting the appropriate fill factor, you can minimize fragmentation and improve overall query performance. Remember to consider the characteristics of your data and workload when determining the right fill factor for your indexes.

Also see below articles

Monitoring and Performance Tuning Tips for SQL Server database
Implement Best Practice and Performance tuning tips in SSIS and ETL Development
SQL Server/Developer Interview Questions and Answers for Experienced Professional
SQL Server/Developer Interview Questions and Answers for Experienced Professional