This section reports asynchronous prefetch activity for all caches.
For information on asynchronous prefetch for each database device, see “Disk I/O management”.
“Total APFs Requested” reports the total number of pages eligible to be pre fetched, 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 detail in the three following categories:
Information about the pages that were pre fetched, “APFs Issued”
Information about the reasons that prefetch was denied
Information about how the page was found in the cache
“APFs Issued” reports the number of asynchronous prefetch requests issued by the system during the sample interval.
This section 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 the “Disk I/O Management” section of the report:
Check the value of the disk i/o structures configuration parameter.
Check values for contention for device semaphores for each database device to determine the source of the problem.
See “Device semaphore granted and waited” for more information.
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. It can be tuned for each pool with sp_poolconfig.
“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.
This section reports how many buffers from APF look-ahead sets were found in the data cache during the sample interval. Asynchronous prefetch tries to find a page it needs to read in the data cache using a quick scan without holding the cache spinlock. If that does not succeed, it then performs a thorough scan holding the spinlock.
