FRAGMENTATION

Fragmentation is unused disk space. Fragmentation is likely to occur with the following:

1. Read/write databases that users are constantly updating with data.
2. Databases that execute calculations around the clock.
3. Databases that frequently update and recalculate dense members.
4. Data loads that are poorly designed.
5. Databases that contain a significant number of Dynamic Calc and Store members.
6. Databases that use an isolation level of uncommitted access with commit block set to zero.

How can we measure fragmentation?

We can measure fragmentation using the Average Clustering Ratio or Average Fragmentation Quotient.

Average Clustering Ratio

The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation.

Average Fragmentation Quotient

Any quotient above the high end of the range indicates that reducing fragmentation may help performance, with the following qualifications:

• The reported value of the Fragmentation Quotient is more accurate when there are no other write transactions running on the database.
• For databases less than 50 MB using the Direct I/O access mode, the fragmentation quotient tends to be high. A high fragmentation quotient does not necessarily indicate a need to reduce fragmentation, because the free space is created in 8 MB chunks and all of it might not get used right away.

How we can prevent and remove fragmentation?

We can prevent and remove fragmentation:

1. To prevent fragmentation, optimize data loads by sorting load records based upon sparse dimension members. For a comprehensive discussion of optimizing data load by grouping sparse members.
2. To remove fragmentation, perform an export of the database, delete all data in the database with CLEARDATA, and reload the export file.
3. To remove fragmentation, force a dense restructure of the database.

With Warm Regards

SST.!