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


Overrides

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

See also

Commands delete, select, set, update

Stored procedures sp_cacheconfig, sp_poolconfig