Status bits in the sysindexes table identify whether a table or an index should be considered for large I/O prefetch or for MRU replacement strategy. By default, both are enabled. To disable or re-enable these strategies, use sp_cachestrategy. The syntax is:
sp_cachestrategy dbname , [ownername.]tablename
[, indexname | "text only" | "table only"
[, { prefetch | mru }, { "on" | "off"}]]
This command turns off the large I/O prefetch strategy 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 or view the cache strategy status of an object.
