user log cache size

user log cache size specifies the size, in bytes, for each user’s log cache. Its size is determined by the server’s logical page size.

Summary Information

Default value

Logical page size

Range of values

2048a –21474836471

Status

Static

Display level

Intermediate

Required role

System administrator

Configuration groups

Memory Use, User Environment

There is one user log cache for each configured user connection and worker process. SAP ASE uses these caches to buffer the user transaction log records, which reduces the contention at the end of the transaction log.

When a user log cache becomes full or another event occurs (such as when the transaction completes), SAP ASE “flushes” all log records from the user log cache to the database transaction log. By first consolidating the log records in each user’s log cache, rather than immediately adding each record to the database’s transaction log, SAP ASE reduces contention of processes writing to the log, especially for SMP systems that are configured with more than one engine.

Note: For transactions using a database with mixed data and log segments, the user log cache is flushed to the transaction log after each log record. No buffering takes place. If your databases do not have dedicated log segments, do not increase the user log cache size.
Do not configure user log cache size to be larger than the maximum amount of log information written by an application’s transaction. Since SAP ASE flushes the user log cache when the transaction completes, any additional memory allocated to the user log cache is wasted. If no transaction in your server generates more than 4000 bytes of transaction log records, set user log cache size no higher than that value. For example:
sp_configure "user log cache size", 4000

Setting user log cache size too high wastes memory. Setting it too low can cause the user log cache to fill up and flush more than once per transaction, increasing the contention for the transaction log. If the volume of transactions is low, the amount of contention for the transaction log may not be significant.

Use sp_sysmon to understand how this parameter affects cache behavior. See the Performance and Tuning Series: Monitoring SAP Adaptive Server with sp_sysmon.

1 a. Minimum determined by server’s logical page size