The output of statistics io reports the number physical reads performed by asynchronous prefetch and the number of reads performed by normal asynchronous I/O. In addition, statistics io reports the number of times that a search for a page in cache was found by the asynchronous prefetch without holding the cache spinlock.
See “Reporting physical and logical I/O statistics” on page 63 in the Performance and Tuning: Monitoring and Analyzing for Performance book for more information.
sp_sysmon report contains information on asynchronous prefetch in both the “Data Cache Management” section and the “Disk I/O Management” section.
If you are using sp_sysmon to evaluate asynchronous prefetch performance, you may see improvements in other performance areas, such as:
Much higher cache hit ratios in the pools where asynchronous prefetch is effective
A corresponding reduction in context switches due to cache misses, with voluntary yields increasing
A possible reduction in lock contention. Tasks keep pages locked during the time it takes for perform I/O for the next page needed by the query. If this time is reduced because asynchronous prefetch increases cache hits, locks will be held for a shorter time.
See “Data cache management” on page 82 and “Disk I/O management” on page 102 in the Performance and Tuning: Monitoring and Analyzing for Performance book for more information.
