You can use optdiag to check the cluster ratio and the number of forwarded rows in tables. Declines in cluster ratios can also indicate that running reorg commands could improve performance:
If the data page cluster ratio for a clustered index is low, run reorg rebuild or drop and re-create the clustered index.
If the index page cluster ratio is low, drop and re-create the non-clustered index.
To reduce the frequency with which you run reorg commands to maintain cluster ratios, increase the reservepagegap slightly before running reorg rebuild.
See Chapter 6, “Statistics Tables and Displaying Statistics with optdiag,” in the book Performance and Tuning: Monitoring and Analyzing for Performance for more information on optdiag.
