Monitoring and managing tables that use expected row size

After setting an expected row size for a table, use optdiag or queries on systabstats to check the number of forwarded rows still being generated by your applications. Run reorg forwarded_rows if you feel that the number of forwarded rows is high enough to affect application performance. reorg forwarded_rows uses short transactions and is very nonintrusive, so you can run it while applications are active.

See the System Administration Guide for more information.

If the application still results in a large number of forwarded rows, you may want to use sp_chgattribute to increase the expected row size for the table.

You may want to allow a certain percentage of forwarded rows. If running reorg to clear forwarded rows does not cause concurrency problems for your applications, or if you can run reorg at non-peak times, allowing a small percentage of forwarded rows does not cause a serious performance problem.

Setting the expected row size for a table increases the amount of storage space and the number of I/Os needed to read a set of rows. If the increase in the number of I/Os due to increased storage space is high, then allowing rows to be forwarded and occasionally running reorg may have less overall performance impact.