Status bits in the sysindexes table identify whether you should consider a table or an index for large I/O prefetch or for MRU replacement strategy. By default, both are enabled. To disable or reenable these strategies, use sp_cachestrategy:
sp_cachestrategy dbname , [ownername.]tablename
[, indexname | "text only" | "table only"
[, { prefetch | mru }, { "on" | "off"}]]
For example, to turn off the large I/O prefetch strategy for the au_name_index of the authors table, enter:
sp_cachestrategy pubtune, authors, au_name_index, prefetch, "off"
To reenable 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.
