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?
Sql-server – SQL Server Log Entry Gathering
sql servertransaction-log
Related Question
- Sql-server – SQL Server Data File/Log File poor performance
- Sql-server – SQL Server: Transaction log still very large after log backup
- Sql-server – Unexpected Transaction Log Flush events and LOG_BACKUP errors
- Sql-server – high writelog wait on OLTP system and fast SSD storage, log flush is slow
- Sql-server – SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database
- Sql-server – Log flushes per second appears low
- Sql-server – TempDB Transaction log not releasing space
- Sql-server – SQL Server options that influence log buffer flushing or commits-per-second performance
Best Answer
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.
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.