When a user performs operations that require logging, log records are first stored in a user log cache until events flush the user’s log records to the current transaction log page in cache. Log records are flushed when:
A transaction ends
The user log cache is full
The transaction changes tables in another database
Another process needs to write a page referenced in the user log cache
Certain system events occur
To economize on disk writes, Adaptive Server holds partially filled transaction log pages for a very brief span of time so that records of several transactions can be written to disk simultaneously. This process is called group commit.
In environments with high transaction rates or with transactions that create large log records, the 2K transaction log pages fill quickly. A large proportion of log writes are due to full log pages, rather than group commits.
Creating a 4K pool for the transaction log can greatly reduce the number of log writes in these environments.
sp_sysmon reports on the ratio of transaction log writes to transaction log allocations. Try using 4K log I/O if all these conditions are true:
The database uses 2K log I/O.
The number of log writes per second is high.
The average number of writes per log page is slightly more than one.
Here is some sample output showing that a larger log I/O size might help performance:
per sec per xact count % of total Transaction Log Writes 22.5 458.0 1374 n/a Transaction Log Alloc 20.8 423.0 1269 n/a Avg # Writes per Log Page n/a n/a 1.08274 n/a
See Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.