“Group Commit Sleeps” reports the number of times a task performed a transaction commit and was put to sleep until the log was written to disk.
Compare this value to the number of committed transactions, reported in “Transaction profile”. If the transaction rate is low, a higher percentage of tasks wait for “Group Commit Sleeps.”
If there are a significant number of tasks resulting in “Group Commit Sleeps,” and the log I/O size is greater than 2K, a smaller log I/O size can help to reduce commit time by causing more frequent page flushes. Flushing the page wakes up tasks sleeping on the group commit.
In high throughput environments, a large log I/O size helps prevent problems in disk queuing on the log device. A high percentage of group commit sleeps should not be regarded as a problem.
Other factors that can affect group commit sleeps are the number of tasks on the run queue and the speed of the disk device on which the log resides.
When a task commits, its log records are flushed from its user log cache to the current page of the transaction log in cache. If the log page (or pages, if a large log I/O size is configured) is not full, the task is switched out and placed on the end of the run queue. The log write for the page is performed when:
Another process fills the log page(s), and flushes the log
When the task reaches the head of the run queue, and no other process has flushed the log page
For more information, see “Choosing the I/O size for the transaction log” on page 234 in Performance and Tuning: Basics.
