The asynchronous prefetch limit specifies the percentage of the pool that can be used to hold pages that have been brought into the cache by asynchronous prefetch, but have not yet been used by any queries. The default value for the server is set with the global async prefetch limit configuration parameter. Pool limits, set with sp_poolconfig, override the default limit for a single pool.
This command sets the percentage for the 2K pool in the pubs_cache to 20:
sp_poolconfig pubs_cache, "2K", "local async prefetch limit=20"
Changes to the prefetch limit for a pool take effect immediately and do not require a restart of Adaptive Server. Valid values are 0–100. Setting the prefetch limit to 0 disables asynchronous prefetching in a pool. For information about the impact of asynchronous prefetch on performance, see See Chapter 10, “Tuning Asynchronous Prefetch,” in Performance and Tuning Guide: Optimizer and Abstract Plans.
