SQL Server: Index Performance Tuning with the Fill Factor

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Recently, I have published a series of articles about the PostgreSQL Fillfactor.

The Fill factor has been a always important parameter for Table and Index so again in this post, I am going to share about the SQL Server Fillfactor.

What is Fill Factor?

In the SQL Server, data stored in an 8KB data page, and by changing a Fill Factor value we can decide that how much we can make page free for future growth and update.

SQL Server Fill Factor is basically for Indexes. The default value for Fill Factor is 0 which is same as 100. You can set Fill factor value between 1 and 100. The value 0 and 100 means no free space on that page and page is full.

Fill factor is an Important parameter:

Carefully we should change the value of Fill Factor otherwise It will be a performance killer for SQL Server.

We should change the Fill factor value from the default 100 to between 70 and 90 bases on data size.

Because, If your index is updating again and again and Fill factor is the default, it requires more new pages to store new data.

It has to move half data into the new pages and has to change the index pointer from old pages to new pages.

This all operation requires more CPU and I/O processes and it creates page level fragmentation by splitting the pages.

If we make some free space available on the same page by changing the Fill factor value, it does not require any extra step to hold new data and saves lots of internal operation and time.

Best Practices:

SQL Server provides two different options to change and set the Fill factor value.
You can change Fill Factor value at SQL Server Instance Level and Index level.

If you change Fill Factor at the instance level, all indexes use this Fill factor value which is not advisable option because we have to change Fill factor value only for more fragmented indexes.

We should analyse the index performance bases on, how data frequently update the indexes and then we should change the Fill factor value for those indexes only.