sp_cachestrategy

Description

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

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.

on | off: specifies the setting, "on" or "off", enclosed in quotes.

Examples

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

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"

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.

When you use sp_cachestrategy without specifying the strategy and setting, it reports the current settings for the object, as shown in Example 1.

To see the size, status and I/O size of all data caches on the server, use sp_cacheconfig.
Setting prefetch "on" has no effect on tables or indexes that are read into a cache that allows only 2K I/O. The mru strategy can be used in all caches, regardless of available I/O size.

Overrides

If prefetching is turned on for a table or an index, you can override the prefetching for a session with set prefetch "off". If prefetching is turned off for an object, you cannot override that setting.

The prefetch, lru, and mru options to the select, delete and update commands suggest the I/O size and cache strategy for individual statements. If prefetching or MRU strategy is enabled for a table or an index, you can override it for a query by specifying I/O the size of the logical page size for prefetch, and by specifying lru strategy. For example, the following command forces LRU strategy, logical page size I/O, and a table scan of the titles table:
```
select avg(advance) 
from titles (index titles prefetch 2 lru)
```
If you request a prefetch size, and the object’s cache is not configured for I/O of the requested size, the optimizer chooses the best available I/O size.
If prefetching is enabled for an object with sp_cachestrategy, using a prefetch specification of the logical page size in a select, update or delete command overrides an earlier set prefetch "on" statement. Specifying a larger I/O size in a select, update or delete command does not override a set prefetch "off" command.

Permissions

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

Granular permissions enabled	With granular permissions enabled, you must be the object owner or a user with manage data cache privilege.
Granular permissions 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:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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