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:

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

Table 10-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

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.