Look-ahead set minimum and maximum sizes

The size of a look-ahead set for a query at a given point in time is determined by several factors:

The type of query, such as a sequential scan or a nonclustered index scan
The size of the pools used by the objects that are referenced by the query and the prefetch limit set on each pool
The fragmentation of tables or indexes, in the case of operations that perform scans
The recent success rate of asynchronous prefetch requests and overload conditions on I/O queues and server I/O limits

Table 16-1 summarizes the minimum and maximum sizes for different type of asynchronous prefetch usage.

**Table 16-1: Look-ahead set sizes**
Access type	Action	Look-ahead set sizes
Table scan Clustered index scan Covered leaf level scan	Reading a page from a new allocation unit	Minimum is 8 pages needed by the query Maximum is the smaller of: The number of pages on an allocation unit that belong to an object. The pool prefetch limits
Nonclustered index scan	Locating qualified rows on the leaf page and preparing to access data pages	Minimum is 2 qualified rows Maximum is the smaller of: The number of unique page numbers on qualified rows on the leaf index page The pool’s prefetch limit
Recovery	Recovering a transaction	Maximum is the smaller of: All of the data and index pages touched by a transaction undergoing recovery The prefetch limit of the pool in the default data cache
Recovery	Scanning the transaction log	Maximum is all pages on an allocation unit belonging to the log
dbcc tablealloc, indexalloc, and checkalloc	Scanning the page chain	Same as table scan
dbcc checktable and checkdb	Scanning the page chain Checking nonclustered index links to data pages	Same as table scan All of the data pages referenced on a leaf level page.