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.

schematable_nameindex_nameFF(%)new FF (%)
ipcTPrimaryTransactionIPT_AmountAM10097
ipcTPrimaryTransactionIPT_ExpID10095
ipcTPrimaryTransactionIPT_PACVATOIDAmAmAmDc10095
ipcTPrimaryTransactionIPT_PrimaryAccount_OID10097
ipcTValueTransactionIVT_TAN10098
ipcTPrimaryAccountIPA_IACCONAACPACOIDDefTA10098

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.

IPCBankerBefore 241,000 insertsAfter 241,000 inserts
Index nameFragm.PagesFragm.Pages
IPT_AccountFolio_OID0.079953930.07997625
IPT_AmountAM0.1510385483.41059734
IPT_AmountDB0.16629400.32664427
IPT_ExpID0.3514521680.531457063
IPT_IACOIDAmAmVATOID0.21190596712.932071323
IPT_IACOIDVATOIDAMDAMA0.0520835360.812094138
IPT_InternalAccount_OID0.419954051.61003186
IPT_PACVATOIDAmAmAmDc0.220835386.342168492
IPT_PrimaryAccount_OID0.2299540410.751069195
IPT_TransactionComponent_OID0.058713070.41873880
IPT_ValueTransaction_OID0.248713030.24873256
IPT_VAT_IAC0.2913691850.431373697
IPT_VATTRCOID0.2212470280.321250875
IPT_VoucherLine_OID0.089954060.08997640
PK__TPrimary__51B1E56F0D84EF7E0.0183639750.018384221

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.

IPCBankerBefore 241,000 insertsAfter 241,000 inserts
Index nameFragmentationPagesFragmentationPages
IPT_AccountFolio_OID0.079976250.08999749
IPT_AmountAM0.0110673120.551071701
IPT_AmountDB0.326644270.53665841
IPT_ExpID0.5314570630.741462123
IPT_IACOIDAmAmVATOID0.0120012010.662009439
IPT_IACOIDVATOIDAMDAMA0.8120941381.112099646
IPT_InternalAccount_OID1.610031861.941006148
IPT_PACVATOIDAmAmAmDc0.2221278592.092153097
IPT_PrimaryAccount_OID0.0510473610.561050825
IPT_TransactionComponent_OID0.418738800.63875810
IPT_ValueTransaction_OID0.248732560.24875115
IPT_VAT_IAC0.4313736970.581378635
IPT_VATTRCOID0.3212508750.471254919
IPT_VoucherLine_OID0.089976400.08999763
PK__TPrimary__51B1E56F0D84EF7E0.0183842210.018403603

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.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

Leave a Reply

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

*