Summary information |
|
|---|---|
Default value |
Logical page size |
Range of values |
2048a –2147483647a. Minimum determined by server’s logical page size |
Status |
Static |
Display level |
Intermediate |
Required role |
System administrator |
Configuration groups |
Memory Use, User Environment |
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. There is one user log cache for each configured user connection and worker process. Adaptive Server 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), Adaptive Server “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, Adaptive Server reduces contention of processes writing to the log, especially for SMP systems that are configured with more than one engine.
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 Adaptive Server 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 Adaptive Server with sp_sysmon.
