sp_cachestrategy

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

Syntax

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

Parameters

Examples

Usage

  • 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.

Permissions

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

SettingDescription
Enabled

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

Disabled

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

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

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
sp_cacheconfig
sp_poolconfig