sp_bindrule  sp_cachestrategy

Chapter 1: System Procedures

sp_cacheconfig

Description

Creates, configures, reconfigures, and drops data caches, and provides information about them.

Syntax

sp_cacheconfig [cachename [ ,"cache_size[P|K|M|G]" ] 
	[,logonly | mixed ] [,strict | relaxed ] ]
	[, "cache_partition=[1|2|4|8|16|32|64]"]

Parameters

cachename

is the name of the data cache to be created or configured. Cache names must be unique, and can be up to 30 characters long. A cache name does not have to be a valid Adaptive Server identifier, that is, it can contain spaces and other special characters.

cache_size

is the size of the data cache to be created or, if the cache already exists, the new size of the data cache. The minimum size of a cache is 256 times the logical page size of the server. Size units can be specified with P for pages, K for kilobytes, M for megabytes, or G for gigabytes. The default is K. For megabytes and gigabytes, you can specify floating-point values. The cache size is in multiples of the logical page size.

logonly | mixed

specifies the type of cache.

strict | relaxed

specifies the cache replacement policy.

cache_partition

specifies the number of partitions to create in the cache.

Examples

Example 1

Creates the data cache pub_cache with 10MB of space. All space is in the default logical page size memory pool:

sp_cacheconfig pub_cache, "10M"

Example 2

Reports the current configuration of pub_cache and any memory pools in the cache:

sp_cacheconfig pub_cache

Example 3

Drops pub_cache at the next start of Adaptive Server:

sp_cacheconfig pub_cache, "0"

Example 4

Creates pub_log_cache and sets its type to logonly in a single step:

sp_cacheconfig pub_log_cache, "2000K", logonly

Example 5

The first command creates the cache pub_log_cache with the default type mixed. The second command changes its status to logonly. The resulting configuration is the same as that in example 4:

sp_cacheconfig pub_log_cache, "2000K"
sp_cacheconfig pub_log_cache, logonly

Example 6

Creates a cache and sets the size, type, replacement policy and number of cache partitions:

sp_cacheconfig 'newcache', '50M', mixed, strict, "cache_partition=2"

Usage


Data cache memory


Changing existing caches


Using cache partitions


Dropping caches

Permissions

Only a System Administrator can execute sp_cacheconfig to change cache configurations. Any user can execute sp_cacheconfig to view cache configurations.

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

System procedures sp_bindcache, sp_helpcache, sp_poolconfig, sp_unbindcache, sp_unbindcache_all





Copyright © 2005. Sybase Inc. All rights reserved. sp_cachestrategy

View this book as PDF