2

I'm trying to better understand how SQL server does log buffering and flushing in order to better balance costs and performance. I know that unless you use delayed durability, log entries for a transaction will be flushed all the way to disk at commit time. However, what I'm really curious about is what else might get flushed along with that. For example, if the log disk is already busy flushing the entries for a previous transaction (and thus can't flush for the current transaction yet), will entries for other transactions also get flushed to disk at the same time, or will the entries for each transaction get flushed serially. Given that in most cases the actual flushing to disk is by far the most time consuming operation and the fact that for logging operations, the time to flush is affected only minimally by the size of the flush (at least for the frequent case of many small transactions), it would seem like a good optimization for the log processor to pipeline flushes such that while one flush is happening, entries from any committed transactions awaiting a flush are gathered together and flushed together rather than one at a time. This would improve throughput with little impact on the latency of the first transaction (it might even improve average latency). Does anyone know if such an optimization has been made in SQL Server? If it has been made in a specific release, can you identify the release and where you found that information?

Improve this question

1 Answer 1

Reset to default
0

However, what I'm really curious about is what else might get flushed along with that.

Any log lower than the LSN currently requesting to be flushed will be flushed as well. Log records are put into the log buffer in order and all transactions are serialized to the log buffer for that database. Note that individual log records aren't flushed, they are put into log blocks and log blocks are flushed.

Does anyone know if such an optimization has been made in SQL Server?

It's called group commit, and it stalls the normal commit for a few ms if it can group more together for better overall performance (roughly-ish speaking). I believe it was added in 2008R2.

Improve this answer
2
  • I'm searching for some reference to that, but can't find anything. "Group Commit" seems to be MySql and Oracle's version of "Delayed Durability".
    – James
    Commented Feb 22, 2017 at 4:33
  • @James It's not delayed durability. It's grouping smaller commits together to get better throughput to the log manager. There isn't anything that's really been blogged about it, but here you can see a performance object for it: msdn.microsoft.com/en-us/library/ms189883.aspx under Group Commit Time/Sec.
    – Sean Gallardy
    Commented Feb 22, 2017 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.