Local asynchronous prefetch is the percentage of buffers in the pool that can be used to hold buffers read into cache by asynchronous prefetch, but that have not yet been used. To change a pool’s asynchronous prefetch percentage:
sp_poolconfig cache_name, "affected_poolK ", "local async prefetch limit=percent "
To change the local synchronous prefetch of named cache log_sales, execute sp_poolconfig 'log sales' on instance SALES_INSTANCE to display:
sp_poolconfig 'log_sales','2K','local async prefetch limit=20','instance SALES_INSTANCE' go
Cache Name Status Type Config Value Run Value ------------------------ ------ ----- ------------ ------------ SALES_INSTANCE:log_sales Active Mixed 100.00 Mb 100.00 Mb (1 row affected) ------------ ------------ Total 100.00 Mb 100.00 Mb ========================================================================== Cache: SALES_INSTANCE:log_sales, Status: Active, Type: Mixed Config Size: 100.00 Mb, Run Size: 100.00 Mb Config Replacement: relaxed LRU, Run Replacement: relaxed LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- ------------- ------------ ------------ ----------- 2 Kb 20480 Kb 0.00 Mb 100.00 Mb 20 (return status = 0)