This is a short post that is considering the indexes fill factor impact on the fragmentation. Analysis of indexes is a continuous process on the company's databases that you as a DBA must follow and monitor well. This post shows how a little bit lower fill factor impacts the fragmentation. I’m going to show how it is with big indexes. By “big” here, the indexes range is in sizes from 10GB to 50GB. Next table lists some of the biggest indexes in a client database.

The indexes fill factor was the default (0) or 100%. The fill factor for them was changed to a different values later.

Indexes with default FILLFACTOR (not changed)

The specific matter here is that the change is tiny, i.e. the fill factor is lowered just a few percentages ranging from 2 – 5%. The reason for it is the size of the indexes compared to the amount of fresh data that enters the table (the indexes) on a daily basis. A tiny volume of data is being inserted during the day, but because it’s an OLAP-like system the majority of new data comes during an over-night loading.

This is the indexes fragmentation after a rebuild algorithm was run on the database.

Figure 1. Fragmentation of the indexes with default fill factor

The maximum fragmentation reached 12% (Figure 1). This is high after one run of the Daily Interface (DI) tool. Some other indexes also got high fragmentation.

Indexes FILLFACTOR changed

I’m repeating the same process, but now with changed fill factor for the indexes. I additionally repeated the test with more inserts. However, the inserts vary on a daily basis ranging from 200K to 400K inserts per a load.

Figure 2. Fragmentation of the indexes with changed fill factor

The maximum fragmentation reached 2%, which is different from the 12% in the previous test.

Discussion

The fill factor for the indexes has the default value of 0 or 100, which means fulfillment of the index (usually B-trees) of 100%. It can be changed for all newly created indexes in the sys.sp_configure on instance level as Database property using the following command:

If you want to change the fill factor for an index, then you can do it only for that index. You can simply rebuild the index with a different fill factor option.

Having lower fill factor causes the number of the page splits are less, but at the same time it makes the memory buffer has more pages. Setting the fill factor is a matter of continuous checking your indexes so that you’re nearing the optimum after a period of monitoring it. An index fragmentation is said to be good if it is below 5 - 10%. For heavy indexes it’s better to keep it lower, and for smaller indexes it’s not a problem to be even higher. However, it’s most important that the fragmentation of indexes is stable.

Final words

You have to maintain your indexes so that they have low fragmentation. Low fragmentation has itself many benefits. It makes queries faster and keeps some wait types low like for example CXPACKET.Indexes which are overloaded with bulked portions of the data is better to have lowered fill factor. There is not a formula what the lowering should be. On different systems I’ve been using lowering from 0 to 30% and it very depends on the size of the index and the nature of the data that is inserted. A good practice is to start with a lowered fill factor and then follow the index fragmentation so until its fragmentation becomes stable.