Asynchronous prefetch and I/O on heap tables

Any task that must perform a physical I/O relinquishes the server’s engine (CPU) while it waits for the I/O to complete. If a table scan must read 1000 pages, and none of those pages are in cache, performing 2K I/O with no asynchronous prefetch means the task makes 1000 loops, executing on the engine, and then sleeping to wait for I/O. Using 16K I/O requires only 125 loops

Asynchronous prefetch speed the performance of queries that perform table scans. 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. Request a page. 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 eight 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.

See Chapter 6, “Tuning Asynchronous Prefetch,” in Performance and Tuning Series: Basics.