Controlling large I/O and cache strategies

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 reenable 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 reenables 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.