Maintaining data cache performance for large I/O

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:

See Chapter 6, “Database Maintenance,” in Performance and Tuning Series: Physical Database Tuning.