The FILL FACTOR impact on the indexes fragmentation

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 changing the fill factor impacts the fragmentation. I’m going to show how it is with big indexes. By “big” here, the indexes range is the sizes range from 10GB to 50GB. Next table lists some of the biggest indexes in a client database.

schema table_name index_name FF(%) new FF (%)
ipc TPrimaryTransaction IPT_AmountAM 100 97
ipc TPrimaryTransaction IPT_ExpID 100 95
ipc TPrimaryTransaction IPT_PACVATOIDAmAmAmDc 100 95
ipc TPrimaryTransaction IPT_PrimaryAccount_OID 100 97
ipc TValueTransaction IVT_TAN 100 98
ipc TPrimaryAccount IPA_IACCONAACPACOIDDefTA 100 98

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

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.

IPCBanker Before 241,000 inserts After 241,000 inserts
Index name Fragm. Pages Fragm. Pages
IPT_AccountFolio_OID 0.07 995393 0.07 997625
IPT_AmountAM 0.15 1038548 3.4 1059734
IPT_AmountDB 0.1 662940 0.32 664427
IPT_ExpID 0.35 1452168 0.53 1457063
IPT_IACOIDAmAmVATOID 0.21 1905967 12.93 2071323
IPT_IACOIDVATOIDAMDAMA 0.05 2083536 0.81 2094138
IPT_InternalAccount_OID 0.41 995405 1.6 1003186
IPT_PACVATOIDAmAmAmDc 0.2 2083538 6.34 2168492
IPT_PrimaryAccount_OID 0.22 995404 10.75 1069195
IPT_TransactionComponent_OID 0.05 871307 0.41 873880
IPT_ValueTransaction_OID 0.24 871303 0.24 873256
IPT_VAT_IAC 0.29 1369185 0.43 1373697
IPT_VATTRCOID 0.22 1247028 0.32 1250875
IPT_VoucherLine_OID 0.08 995406 0.08 997640
PK__TPrimary__51B1E56F0D84EF7E 0.01 8363975 0.01 8384221

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.

IPCBanker Before 241,000 inserts After 241,000 inserts
Index name Fragmentation Pages Fragmentation Pages
IPT_AccountFolio_OID 0.07 997625 0.08 999749
IPT_AmountAM 0.01 1067312 0.55 1071701
IPT_AmountDB 0.32 664427 0.53 665841
IPT_ExpID 0.53 1457063 0.74 1462123
IPT_IACOIDAmAmVATOID 0.01 2001201 0.66 2009439
IPT_IACOIDVATOIDAMDAMA 0.81 2094138 1.11 2099646
IPT_InternalAccount_OID 1.6 1003186 1.94 1006148
IPT_PACVATOIDAmAmAmDc 0.22 2127859 2.09 2153097
IPT_PrimaryAccount_OID 0.05 1047361 0.56 1050825
IPT_TransactionComponent_OID 0.41 873880 0.63 875810
IPT_ValueTransaction_OID 0.24 873256 0.24 875115
IPT_VAT_IAC 0.43 1373697 0.58 1378635
IPT_VATTRCOID 0.32 1250875 0.47 1254919
IPT_VoucherLine_OID 0.08 997640 0.08 999763
PK__TPrimary__51B1E56F0D84EF7E 0.01 8384221 0.01 8403603

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 fulfilment 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*