Diagnosing Fragmentation

Go Up to Space Analyst Expert Guide

If you have an environment that will benefit from reducing fragmentation, how do you go about recognizing it? There are a number of metrics and factors to consider before you can intelligently start creating reorganization plans.

When diagnosing fragmentation, pay particular attention to the following metrics:

• Extent Fragmentation - this highlights any gaps (lack of contiguous order) in extents and indicates the presence of external fragmentation. Percentages of 25-50% or more are not a favorable reading.
• Logical Fragmentation - this metric represents the percentage of pages that are out of physical order. High values (greater than 25%) for this metric may mean that index scan performance is not what it could be. Note that this statistic is meaningless for tables without clustered indexes (heap tables).
• Average Page Density - this metric represents the fullness of the data or index pages, and is an indicator of internal fragmentation. Remember that the more full a data or index page is, the less I/O needed by IBM DB2 when performing scans of tables or indexes. High values are good here, with anything below 50% being suspect. Low values for this metric often indicate the presence of page splitting. Keep in mind, however, that internal fragmentation isn't necessarily bad in OLTP environments where large FILLFACTORs are often specified. Also understand that small objects (those will little or no data) will likely show low readings because they simply don't consume enough space in a database page. You can ignore these objects.
• Extent Switches - in a perfect world, this will equal # of object extents - 1. Higher values (values many times higher than an object's extent count) can indicate external fragmentation.
• Scan Density - this is computed by dividing the optimum number of extent switches by the actual number of extent switches. This percentage can be misleading, however, if the object spans more than one database file and should not be considered in such an event. Values close to 100% are best.

As you can see, you can't just blindly diagnose fragmentation in IBM DB2, but instead have to evaluate a number of factors.