Enables or disables prefetching (large I/O) and MRU cache replacement strategy for a table, index, text object, or image object.


sp_cachestrategy dbname, [ownername.]tablename 
	[, indexname | "text only" | "table only" 
	[, {prefetch | mru}, {"on" | "off"}]]




  • If memory pools for large I/O are configured for the cache used by a table or an index, the optimizer can choose to prefetch data or index pages by performing large I/Os of up to eight data pages at a time. This prefetch strategy can be used on the data pages of a table or on the leaf-level pages of a nonclustered index. By default, prefetching is enabled for all tables, indexes, and text or image objects. Setting the prefetch option to “off” disables prefetch for the specified object.

  • The optimizer can choose to use MRU replacement strategy to fetch and discard buffers in cache for table scans and index scans for I/O of any size. By default, this strategy is enabled for all objects. Setting mru to “off”disables this strategy. If you turn mru off for an object, all pages are read into the MRU/LRU chain in cache, and they remain in the cache until they are flushed by additional I/O. For more information on cache strategies, see the Performance and Tuning Guide.

  • You can change the cache strategy only for objects in the current database.

  • To see the size, status and I/O size of all data caches on the server, use sp_cacheconfig.

See also delete, select, set, update in Reference Manual: Commands.


The permission checks for sp_cachestrategy differ based on your granular permissions settings.


With granular permissions enabled, you must be the object owner or a user with manage data cache privilege.


With granular permissions disabled, you must be the object owner or a user with sa_role.


Values in event and extrainfo columns from the sysaudits table are:



Audit option


Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference