sp_cachestrategy

Syntax

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

dbname – is the name of the database where the object is stored.
ownername – is the name of the table’s owner. If the table is owned by “dbo”, the owner name is optional.
tablename – is the name of the table.
indexname – is the name of the index on the table.
text only – changes the cache strategy for a text or image object.
table only – changes the cache strategy for a table.
prefetch | mru – is prefetch or mru, and specifies which setting to change. You can use the mru strategy in all caches, regardless of available I/O size. Setting prefetch "on" has no effect on tables or indexes that are read into a cache that allows only 2K I/O.
on | off – specifies the setting, "on" or "off", enclosed in quotes.

Example 1 – Displays information about cache strategies for the titles table:

sp_cachestrategy pubs2, titles

object name       index name    large IO  MRU
----------------- ------------- --------  --------
dbo.titles        titleidind    ON         ON

When you use sp_cachestrategy without specifying the strategy and setting, it reports the current settings for the object.

Example 2 – Displays information about cache strategies for the titleind index:
```
sp_cachestrategy pubs2, titles, titleind
```
Example 3 – Disables prefetch on the titleind index of the titles table:
```
sp_cachestrategy pubs2, titles, titleind, prefetch, "off"
```
Example 4 – Reenables MRU replacement strategy on the authors table:
```
sp_cachestrategy pubs2, authors, "table only", mru, "on"
```
Example 5 – Reenables prefetching on the text pages of the blurbs table:
```
sp_cachestrategy pubs2, blurbs, "text only", prefetch, "on"
```

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.

Setting	Description
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.

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

Information	Values
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