You can measure fragmentation using the average fragmentation quotient statistic. In ESSCMD, look at the Average Fragmentation Quotient that is returned when you execute the GETDBSTATS command. Use the information in Table 168 to evaluate whether the level likely will cause performance problems:
Table 168. Measuring Fragmentation Thresholds Using the Average Fragmentation Quotient
Database Size | Fragmentation Quotient Threshold |
---|---|
Small (<200 MB) | 60% or greater |
Medium (<2 GB) | 40% or greater |
Large (>2 GB) | 30% or greater |
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 no other write transactions run 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 immediately.
The average clustering ratio database statistic is an approximate indication of the ordering in which data blocks are laid out in data (.pag) files. The maximum value of 1 indicates that the blocks are laid out in block key order within data files. The reported value of the clustering ratio may be less than 1, even upon restructure, due to the scalability enhancements as well as support for parallel operations.
.