Reports asynchronous prefetch activity for all caches.
For information on asynchronous prefetch for each database device, see “Disk I/O Management”. For information about asynchronous prefetch, see Chapter 6, “Tuning Asynchronous Prefetch,” in Performance and Tuning Series: Basics.
“Total APFs Requested” reports the total number of pages eligible to be prefetched, that is, the sum of the look-ahead set sizes of all queries issued during the sample interval. Other rows in “Asynchronous Prefetch Activity” provide detailed information about:
The pages that were prefetched (“APFs Issued”)
The reasons that prefetch was denied
The page was found in the cache
Number of asynchronous prefetch requests issued by the system during the sample interval.
Reports the reasons that APFs were not issued:
“APF I/O Overloads” reports the number of times APF usage was denied because of a lack of disk I/O structures or because of disk semaphore contention.
If this number is high, check the following information in “Disk I/O Management:”
The value of the disk i/o structures configuration parameter. See “Disk I/O Structures”.
Values for contention for device semaphores for each database device to determine the source of the problem. See “Mirror Semaphore Granted and Waited”.
If the problem is due to a shortage of disk I/O structures, set the configuration parameter higher, and repeat your tests. If the problem is due to high disk semaphore contention, examine the physical placement of the objects where high I/O takes place.
“APF Limit Overloads” indicates that the percentage of buffer pools that can be used for asynchronous prefetch was exceeded. This limit is set for the server as a whole by the global async prefetch limit configuration parameter. Use sp_poolconfig to tune each pool individually.
“APF Reused Overloads” indicates that APF usage was denied due to a kinked page chain or because the buffers brought in by APF were swapped out before they could be accessed.
Ideally, consecutive pages contain consecutive rows. However, if the page chain jumped from its present position to a distant piece of disk for a few pages then jumped back to its original position, the page is considered “kinked”. For example, a page chain that uses pages 1, 2, 396, 397, 3, 4 is kinked.
Number of buffers from APF look-ahead sets that were found in the data cache during the sample interval. Asynchronous prefetch tries to find a page to read in the data cache using a quick scan, without holding the cache spinlock. If that does not succeed, APF then performs a thorough scan holding the spinlock.
