In most cases, when you specify an I/O size in a query, the query processor 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.
You cannot use large I/O for the query if:
The cache is not configured for I/O of the specified size. The query processor substitutes the best size available.
sp_cachestrategy has been used to disable large I/O for the table or index.
You cannot use large I/O 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.
When 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 the Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.
