Asynchronous prefetch helps speed the performance of queries that perform table scans. Any task that needs to perform a physical I/O relinquishes the server’s engine (CPU) while it waits for the I/O to complete.
If a table scan needs to read 1000 pages, and none of those pages are in cache, performing 2K I/O with no asynchronous prefetch means that the task would make 1000 loops, executing on the engine, and then sleeping to wait for I/O. Using 16K I/O would required only 125 such loops.
Asynchronous prefetch can request all of the pages on an allocation unit that belong to a table when the task fetches the first page from the allocation unit. If the 1000-page table resides on just 4 allocation units, the task requires many fewer cycles through the execution and sleep loops.
Type of I/O |
Loops |
Steps in each loop |
|---|---|---|
2K I/O no prefetch |
1000 |
Request a page. Sleep until the page has been read from disk. Wait for a turn to run on the Adaptive Server engine (CPU). Read the rows on the page. |
16K I/O no prefetch |
125 |
Request an extent. Sleep until the extent has been read from disk. Wait for a turn to run on the Adaptive Server engine (CPU). Read the rows on the 8 pages. |
Prefetch |
4 |
Request all the pages in an allocation unit. Sleep until the first page has been read from disk. Wait for a turn to run on the Adaptive Server engine (CPU). Read all the rows on all the pages in cache. |
Actual performance depends on cache size and other activity in the data cache.
For more information on asynchronous prefetching, see Chapter 27, “Tuning Asynchronous Prefetch.”
