"I've Reorganized, But It Didn't Help!"

Go Up to Space Analyst Expert Guide

Sometimes, a DBA will reorganize a table or index and no measurable benefits are noticed afterwards. The same or worse fragmentation metrics are observed for the objects in question. What could have gone wrong?

When attempting the reorganization of either tables or indexes, you have to understand that only objects that are of a significant size will show improvement from a reorganization. IBM DB2 for Linux, Unix, and Windows has stated that objects with less than 1,000 pages will oftentimes offer no performance improvements whatsoever from being reorganized.

Other points to keep in mind include:

• Tables without indexes receive zero benefit (naturally) from running either a DBCC DBREINDEX or a DBCC INDEXDEFRAG. The only way to reorganize a table without indexes is to create a clustered index on the table and then immediately drop it.
• Heap tables should not be diagnosed as having fragmentation through the logical scan fragmentation measure. This metric is meaningless for such tables.
• As mentioned above, small objects seldom benefit from reorganizations. For example, the page density for a table with only 10 rows in it will likely not be high, and reorganizing it won't change things one bit. You need to keep such things in mind when determining whether to reorganize an object or not. Space Analyst keeps you informed of not only reorganization metrics but object metrics (extent size, row counts, etc.) as well so you can make smart reorganization choices.
• Lack of contiguous free space can result in all fragmentation not being eliminated. Without large amounts of contiguous free space, reorganization operations may have to reuse other areas of space within the database files. The end result is oftentimes an index being reorganized, but still having an amount of logical scan fragmentation.