Asynchronous prefetch improves performance by anticipating the pages that will be required for certain well-defined classes of database activities whose access patterns are predictable. The I/O requests for these pages are issued before the query needs them so that most pages are in cache by the time query processing needs to access the page.
Asynchronous prefetch can improve performance for:
Sequential scans, such as table scans, clustered index scans, and covered nonclustered index scans
The update statistics command
Accesses via nonclustered indexes
Some dbcc checks
Recovery
Asynchronous prefetch can improve the performance of queries that access large numbers of pages, such as decision support system (DSS) applications, as long as the I/O subsystems on the machine are not saturated.
Asynchronous prefetch cannot help (or may help only slightly) when the I/O subsystem is already saturated or when Adaptive Server is CPU-bound. It can be used in some online transaction processing (OLTP) applications, but to a much lesser degree, since the queries in OLTP applications usually perform fewer I/O operations.
The default setting for global async prefetch
limit is 10, meaning that asynchronous prefetch is enabled
when you install or upgrade to release 11.5, and that all
pools can use up to 10 percent of the buffers for asynchronous reads.
Table 5-1 shows where to find additional information on asynchronous prefetch.
For information On |
See |
|---|---|
How queries can benefit from asyncronous prefetch |
Chapter 18, “Tuning Asynchronous Prefetch,” in the Performance and Tuning Guide |
Configuring pool limits |
Chapter 9, “Configuring Data Caches,” in the System Administration Guide |
Configuring server-wide limits |
Chapter 11, “Setting Configuration Parameters,” in the System Administration Guide |
Tuning asynchronous prefetch limits |
Chapter 24, “Monitoring Performance with sp_sysmon,” in the Performance and Tuning Guide |
