When heap tables, clustered indexes, or nonclustered indexes have just been 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.
When this value is 1, or close to 1, large I/O is very efficient. As the value drops, 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.
You need to consider rebuilding indexes when large I/O efficiency drops or activity in the pool increases due to increased 16K I/O.
When large I/O efficiency drops, 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.
For more information, see “Running reorg on tables and indexes”.
