When using a nonclustered index to access rows, asynchronous prefetch finds the page numbers for all qualified index values on a nonclustered index leaf page. It builds the look-ahead set from the unique list of all the pages that are needed.
Asynchronous prefetch is used only if two or more rows qualify.
If a nonclustered index access requires several leaf-level pages, asynchronous prefetch requests are also issued on the leaf pages.
