When a user performs operations that require logging, log records are first stored in a “user log cache” until certain events flush the user’s log records to the current transaction log page in cache. Log records are flushed:
When a transaction ends
When the user log cache is full
When the transaction changes tables in another database
When another process needs to write a page referenced in the user log cache
At certain system events
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 transactions that create large log records, the 2K transaction log pages fill quickly, and 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. You should try using 4K log I/O if all of these conditions are true:
Your database is using 2K log I/O.
The number of log writes per second is high.
The average number of writes per log page is slightly above 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 “Transaction log writes” on page 59 in the book Performance and Tuning: Monitoring and Analyzing for Performance for more information.
