The optimizer and cache choices

If the cache for a table or index has a 16K pool, the optimizer determines the I/O size to use for data and leaf-level index pages based on the number of pages that must be read, and the cluster ratios for the table or index.

The optimizer’s knowledge is limited to the single query it is analyzing and to statistics about the table and cache. It does not know how many other queries are simultaneously using the same data cache. It has no statistics on whether table storage is fragmented such that large I/Os or asynchronous prefetch would be less effective.

In some cases, this combination of factors can lead to excessive I/O. For example, users may experience higher I/O and poor performance if simultaneous queries with large result sets are using a very small memory pool.