sp_cacheconfig

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

Syntax

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

Parameters

Examples

Usage

  • The minimum cache size is 256 times the logical page size. For example, a 4K server would have a minimum cache size of 1024K.

  • If the SAP ASE server is unable to allocate all the memory requested while you are creating a new cache or adding memory to an existing cache, it allocates all the available memory. However, this additional memory is allocated at the next restart of the SAP ASE server.

  • If there are objects bound to cache (including the default cache), you cannot delete the cache until you unbind the objects.

  • (In cluster environments) If you do not specify an instance_name, the cache for the cluster is displayed.

  • Some of the actions you perform with sp_cacheconfig are dynamic (do not require a restart of the SAP ASE server) and some are static (require a reboot). The dynamic and static actions are:

    Dynamic sp_cacheconfig Actions

    Static sp_cacheconfig Actions

    Adding a new cache

    Changing the number of cache partitions

    Adding memory to an existing cache

    Reducing a cache size

    Deleting a cache

    Changing the replacement policy

    Changing a cache type

  • When you first create a data cache:
    • All space is allocated to the logical page size memory pool.

    • The default type is mixed.

  • This figure shows a data cache for a 2K server with two user-defined data caches configured and the following pools:
    • The default data cache with a 2K pool and a 16K pool

    • A user cache with a 2K pool and a 16K pool

    • A log cache with a 2K pool and a 4K pool

      Data Cache With Default and User-Defined Caches
      This figure shows the data cache with default and user-defined caches for the sp_cacheconfig stored procedure.
  • The default data cache must always have the type default, and no other cache can have the type default.

  • The SAP ASE housekeeper task does not do any buffer washing in caches with a type of logonly or in caches with a relaxed LRU replacement policy.

  • The following commands perform only 2K I/O: disk init, some dbcc commands, and drop table. The dbcc checkdb and dbcc checktable commands can perform large I/O for tables, but perform 2K I/O on indexes. Cache usage for Transact-SQL commands, depending on the binding of the database or object, are:

    Command

    Database Bound

    Table or Index is Bound

    Database or Object Not Bound

    create index

    Bound cache

    N/A

    Default data cache

    disk init

    N/A

    N/A

    Default data cache

    dbcc checkdb

    Bound cache

    N/A

    Default data cache

    dbcc checktable, indexalloc, tablealloc

    Bound cache

    Bound cache

    Default data cache

    drop table

    Bound cache

    Bound cache

    Default data cache

  • Recovery uses only the logical page size pool of the default data cache. All pages for all transactions that must be rolled back or rolled forward are read into and changed in this pool. Be sure that your default logical page size pool is large enough for these transactions.

  • When you use sp_cacheconfig with no parameters, it reports information about all of the caches on the server. If you specify only a cache name, it reports information about only the specified cache. If you use a fragment of a cache name, it reports information for all names matching “%fragment%”.

    All reports include a block of information that reports information about caches, and a separate block of data for each cache that provides information about the pools within the cache.

    The output below, from a server using 2K, shows the configuration for:
    • The default data cache with two pools: a 2K pool and a 16K pool. The default data cache has 2 partitions.

    • pubs_cache with two pools: 2K and 16K

    • pubs_log, with the type set to logonly and cache replacement policy set to relaxed, with a 2K pool and a 4K pool

      Cache Name               Status    Type     Config Value  Run Value
       ----------------------- --------- -------- ------------ --------
      default data cache        Active   Default      0.00 Mb   26.09 Mb 
      pubs_cache               Active    Mixed        10.00 Mb  10.00 Mb 
      pubs_log                 Active    Log Only      2.40 Mb    2.40 M
                                                    ------------- --------
                                            Total     12.40 Mb   38.49 Mb
      ===================================================================
      Cache: default data cache,   Status: Active,   Type: Default
            Config Size: 0.00 Mb,   Run Size: 26.09 Mb
            Config Replacement: strict LRU,   Run Replacement: strict LRU
            Config Partition:            2,   Run Partition:            2
       IO Size  Wash Size Config Size  Run Size     APF Percent 
       -------- --------- ------------ ------------ ----------- 
           2 Kb   3704 Kb      0.00 Mb     18.09 Mb     10
          16 Kb   1632 Kb      8.00 Mb      8.00 Mb     10
      ===================================================================
      Cache: pubs_cache,   Status: Active,   Type: Mixed
            Config Size: 10.00 Mb,   Run Size: 10.00 Mb
            Config Replacement: strict LRU,   Run Replacement: strict LRU
            Config Partition:            1,   Run Partition:            1
       IO Size  Wash Size Config Size  Run Size     APF Percent 
       -------- --------- ------------ ------------ ----------- 
           2 Kb   1228 Kb      0.00 Mb      6.00 Mb     10
          16 Kb    816 Kb      4.00 Mb      4.00 Mb     10
      ===================================================================
      Cache: pubs_log,   Status: Active,   Type: Log Only
            Config Size: 2.40 Mb,   Run Size: 2.40 Mb
            Config Replacement: relaxed LRU,   Run Replacement: relaxed LRU
            Config Partition:            1,   Run Partition:            1
       IO Size  Wash Size Config Size  Run Size     APF Percent 
       -------- --------- ------------ ------------ ----------- 
           2 Kb    206 Kb      0.00 Mb      1.01 Mb     10
          16 Kb    272 Kb      1.40 Mb      1.39 Mb     10 

    The meaning of the columns in the output are:

    ColumnMeaning
    Cache Name
    The name of the cache.
    Status
    One of the following:
    • “Active”

    • “Pend/Act”

    • “Pend/Del”

      The status “Pend” is short for pending. It always occurs in combination with either “Act” for Active or “Del” for Delete. It indicates that a configuration action has taken place, but that the server must be restarted in order for the changes to take effect.

    Type

    “Mixed” or “Log Only” for user-defined caches, “Default” for the default data cache.

    I/O Size

    The size of I/O for a memory pool. This column is blank on the line that shows that cache configuration.

    Wash Size

    The size of the wash area for the pool. As pages enter the wash area of the cache, they are written to disk. This column is blank on the line that shows the cache configuration.

    Config Value or Config Size

    The size that the cache or pool. If the value is 0, the size has not been explicitly configured, and a default value is used.

    Run Value or Run Size

    The size of the cache or pool now in use on the SAP ASE server.

    Config/ Run Replacement

    The cache policy (strict or relaxed) that is used for the cache after the next restart, and the current replacement policy. These differ only if the policy has been changed since the last reboot.

    Config/Run Partition

    The number of cache partitions that is used for the cache, and the current number of partitions. These differ if sp_cacheconfig has been used to change the number of partitions since the last reboot.

    APF Percent

    The percentage of buffers in the pool that can hold buffers that have been fetched by asynchronous prefetch, but have not been used.

    Total

    The total size of data cache, if the report covers all caches, or the current size of the particular cache, if you specify a cache name.

    Effects of Restarts and sp_cacheconfig on Cache Status
    This figure shows the effects of restarts and sp_cacheconfig on cache status.
  • You can also configure caches and pools by editing the configuration file. For more information, see the System Administration Guide.

Permissions

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

SettingDescription
Enabled

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

Any user can execute sp_cacheconfig to view cache configurations

Disabled

With granular permissions disabled, you must be a user with sa_role.

Any user can execute sp_cacheconfig to view cache configurations

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_bindcache
sp_helpcache
sp_poolconfig
sp_unbindcache
sp_unbindcache_all