When heap tables, clustered indexes, or nonclustered indexes are newly created, they show optimal performance when large I/O is being used. Over time, the effects of deletes, page splits, and page deallocation and reallocation can increase the cost of I/O. optdiag reports a statistic called “Large I/O efficiency” for tables and indexes.
A large I/O is very efficient when this value is 1, or close to 1. As the value decreases, more I/O is required to access data pages needed for a query, and large I/O may be bringing pages into cache that are not needed by the query.
Consider rebuilding indexes when large I/O efficiency drops or when activity in the pool increases due to increased 16K I/O.
When large I/O efficiency decreases, you can:
Run reorg rebuild on tables that use data-only-locking. You can also use reorg rebuild on the index of data-only-locked tables.
For allpages-locked tables, drop and re-create the indexes.
See Chapter 6, “Database Maintenance,” in Performance and Tuning Series: Physical Database Tuning.