The Log Flushes (Per Second) metric reads LOG FLUSHES/SEC from sysperfinfo. Each time data changes, the changes write to the log cache, which resides in memory. Under certain conditions, this log cache is flushed to disk. These situations include:
- A commit or roll back of an explicit or implicit transaction.
- A CHECKPOINT statement execution.
- SQL Server issues an automatic checkpoint.
- The Log Cache is full and needs emptying.
Reduce log flushes
On an OLTP system that runs relatively small transactions (on average, less than 40 rows added, changed, or deleted per transaction), the number of Log Flushes should not exceed SQL Batches by more than 40% during periods of high activity. If you exceed this percentage, then one of the following abnormal conditions exists:
- T-SQL batches sent for execution contain a number of individual data manipulation statements (inserts, deletes, or updates) not enclosed within a transaction.
- Large batch jobs which update significant amounts of data are run during the day.
- Deadlocks or Lock Timeouts are causing a number of transactions to roll back or execute a ROLLBACK TRANSACTION statement.
- Your Recovery Interval parameter is too low. Raise this parameter to 10-15 minutes.
- An explicit CHECKPOINT statement is executed within some transactions.
- If you need to delete the entire contents of a table, use the TRUNCATE TABLE statement instead of DELETE as it produces less log flushes.
If you implement the previously-mentioned measures and the Log Flushes still greatly exceed 140% of SQL Batches, then consider:
- Switching your RAID array from a RAID 5 to a RAID 10 solution. Each write IO results in two writes using RAID 10 vs. 4 for RAID 5 (100% more efficient with writes).
- As long as the RAID controller has some form of battery backup, then switch its cache mode from Write-through to Write-back, as this setting increases the system's ability to handle write IOs by an order of magnitude.
- Adding more cache memory to the RAID controller.