What is the Best Value for the Fill Factor? Index, Fill Factor and Performance, Part 2

I have received many emails for this performance tuning series. I am glad that many are appreciating the blog series and its sincere aim to address the business processes and real world consultation experience.

In the earlier blog post (What is Fill Factor? Index, Fill Factor and Performance – Part 1), we have understood Fill Factor, page split and various methods of retrieving various values of the Fill Factor. In this blog post, we will discuss the same and further discuss about what are the optimal settings for the same.

What is the best value of Fill Factor?

This is complicated question, and it is very difficult to answer without checking the workload of the system.

My Methodology for Fill Factor

Fill Factor matters at the two granular levels – server level and table/index level. We will discuss both the levels, one at a time.

Server Level Fill Factor

Before I start the project, I noted the counter “SQLServer:AccessMethods:Page Splits/Sec”. I lower the Fill Factor values to 95, 90, 85 and 80 and observed the counter over several days. Please note this process requires to rebuild all the indexes which may not be recommended. When I find the optimal (least) value for the counter, I consider that particular value as the optimal value for my Fill Factor. Please note this is not the best practice but something you should be aware of it.  

Thus far, all the discussions focused on the server level, and we assume that one server level setting will fix all the issues. Just like everything, there are many more things to discuss at the object level.

We are currently trying to find an optimal Fill Factor which leads us to least number of page splits and conservative usage of the space as well. The method which I have discussed earlier surely works at the server level if it is combined with the understanding of the index-level Fill Factor.

Earlier, I have blogged regarding how to change the server level Fill Factor. Click the following to read the article: Set Server Level FILLFACTOR Using T-SQL Script.

Index Level Fill Factor

We can set one generic server level Fill Factor, but that setting can be over-ridden by us at each index level. If we do not over ride it, then all the index will follow the server-level settings.

Now, I am going to roughly categorize the user tables into the following:

Earlier, I had blogged about how to change the table-level Fill Factor earlier as well the differences from the SQL Server 2000 method. Click Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild to read the complete article.

Here is a quick example for the same:

USE [AdventureWorks]GOALTERINDEX [PK_Product_ProductID] ON [Production].[Product]REBUILDPARTITION=ALLWITH (FILLFACTOR= 90)GO

Now I try to analyze the entire database tables keeping the above four categories in my mind. Based on my categorization, I set the Fill Factor.

Static Tables – Set Fill Factor at 100 (or default server fill factor),

As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation.

Tables Updated Less Often – Set Fill Factor at 95.

These tables are more or less having characteristics like static tables. As they are not updated often, they do not encounter much issues. To accommodate the updates, I like to keep the Fill Factor 95. Honestly, if you are rebuilding the indexes at regular intervals, then I would prefer a Fill Factor of 95.

Frequently updated Tables – Set Fill Factor between 70 and 90.

When I have to set the Fill Factor at the table level, I first start from 90 and observe the table for a while, If I notice that there is still a recurring issue with page split, which in turn leads to fragmentation, I lower it further down towards 70 with an interval of the 5 at one times. Fill factor has to main balance between reads/writes.

Tables with Clustered Index on Identity Column – Set Fill Factor at 100.

This is very often seen in an OLTP system. Many tables have the identity column as a clustered index. In this case, all the new data is always inserted at the end of table and a new row is never inserted in the middle of the table. In this situation, the value of Fill Factor does not play any significant role and it is advisable to have the Fill Factor set to 100.

Fill Factor and Rebuild Indexes

While working closely with DBA and Developers in industry, I have quite often come across a simple misconception or confusion regarding Fill Factor. It is very much known that when page is full and additional rows are inserted into the page, “page split” happens. What is often confused is the split ratio. Many assume that split happens because of the Fill Factor. The fact is that split is always 50-50. Fill Factor is only applicable when an index is rebuilt or when a new index is created.

This leads to a very interesting observation about rebuilding indexes. Rebuilding index will re-arrange every page and data (in other words tidy up the space) based on the Fill Factor. Every page will have exactly the same amount of the data as that specified in the Fill Factor.

I quite often receive the question regarding how often one should rebuild indexes. This question is one of those questions which cannot be answered without considering the workload. Here is quick opinion: I rebuild my “fragmented” large indexes at least once a week based on a widely used algorithm. I will discuss about this algorithm in one of my future posts.

Quick Opinion about Fill Factor

In last two post, we have understood a lot about Fill Factor and I am sure now you are also clear that there is no right answer for the same. When I look at the value in configuration, I give the following opinion to my client right away.

If the Fill Factor is 100, I usually say, it needs to be lowered for the frequently updated table. If Fill Factor is lower than 100, I usually say, it needs to be set to 100 for identity columns and master tables. Additionally, I briefly discuss about how I would like to implement the index de-fragmentation algorithm.

In fact, I have always noticed that briefing the client about this subject makes them confident regarding the skills of the consultant. I am very particular about explaining the importance of Fill Factor to the client, which in turn buys me more time from them to improve their Fill Factor strategies and index tuning methodology.

In next blog posts, we will continue our series of quick look at configuration and discuss the specifics in depth. Meanwhile, I encourage you all to visit my blog SQL Authority.com for additional information.