Status bits in sysindexes identify whether a table or index should be considered for prefetching or for MRU replacement strategy. By default, both are enabled. To disable or re-enable these strategies, use the sp_cachestrategy system procedure. The syntax is:
sp_cachestrategy dbname , [ownername.]tablename
[, indexname | "text only" | "table only"
[, { prefetch | mru }, { "on" | "off"}]]
This command turns the prefetch strategy off for the au_name_index of the authors table:
sp_cachestrategy pubtune, authors, au_name_index, prefetch, "off"
This command re-enables MRU replacement strategy for the titles table:
sp_cachestrategy pubtune, titles, "table only", mru, "on"
Only a System Administrator or the object owner can change the cache strategy status of an object.
To see the cache strategy in effect for a given object, execute sp_cachestrategy, including only the database and object name:
sp_cachestrategy pubtune, titles
object name index name large IO MRU ---------------- ---------------- -------- -------- titles NULL ON ON
showplan output shows cache strategy for each object, including worktables.