Choosing the I/O size for the transaction log

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:

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:

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.