In high throughput environments with a large number of concurrent users committing transactions, a certain amount of contention for the log semaphore is expected. In some tests, very high throughput is maintained, even though log semaphore contention is in the range of 20 to 30%.
In SMP environments with high rates of data modification transactions, use the dbcc tune(log_prealloc) command if log semaphore contention is high. A system task, the log allocator process, performs transaction log allocations, reducing the time that each task holds the log semaphore.
To start the log allocator process for a database, use:
dbcc tune(log_prealloc, dbid, "on")
The log allocator loops through databases in a way similar to the checkpoint process, except that a System Administrator chooses which databases need log pre allocation. The log allocator runs once per minute.
When the log allocator is enabled for a database, it preallocates 64 log pages at a time.
Some options for reducing log semaphore contention are:
Increasing the ULC size, if filling user log caches is a frequent cause of user log cache flushes.
See “ULC flushes to transaction log” for more information.
Reducing log activity through transaction redesign. Aim for more batching with less frequent commits. Be sure to monitor lock contention as part of the transaction redesign.
Reducing the number of multi database transactions, since each change of database context requires a log write.
Dividing the database into more than one database so that there are multiple logs. If you choose this solution, divide the database in such a way that multi database transactions are minimized.
