In most cases, when you specify an I/O size in a query, the optimizer incorporates the I/O size into the query’s plan. However, there are times when the specification cannot be followed, either for the query as a whole or for a single, large I/O request.
Large I/O cannot be used for the query if:
The cache is not configured for I/O of the specified size. The optimizer substitutes the best size available.
sp_cachestrategy has been used to disable large I/O for the table or index.
Large I/O cannot be used for a single buffer if
Any of the pages included in that I/O request are in another pool in the cache.
The page is on the first extent in an allocation unit. This extent holds the allocation page for the allocation unit, and only seven data pages.
No buffers are available in the pool for the requested I/O size.
Whenever a large I/O cannot be performed, Adaptive Server performs 2K I/O on the specific page or pages in the extent that are needed by the query.
To determine whether the prefetch specification is followed, use showplan to display the query plan and statistics io to see the results on I/O for the query. sp_sysmon reports on the large I/Os requested and denied for each cache.
See “Data cache management” on page 278 in the book Performance and Tuning: Monitoring and Analyzing for Performance.
