Log Bytes Flushed/sec

This performance counter shows the average log bytes flushed per second for the current database since the last data point.

The value returned helps identify trends of the transaction log. A log flush happens when data is written from the log cache to the transaction log on disk, each time a transaction happens.

This is not available in SQL 2012.

Potential Issue: The rate at which log records are written to the disk. This is the log generation rate of the application. It plays a very important role in determining database mirroring performance. This counter is in the Databases performance object.

Log Bytes Flushed/sec can indicate many small transactions leading to high mechanical disk activity.

Remediation:
Look at the statements associated with the log flush and determine if the number of log flushes can be reduced. When a single transaction is used, the log records for the transaction can be bundled and a single, larger write can be used to flush the gathered log records. The mechanical intervention is significantly reduced. It is not recommended that you increase your transaction scope. Long-running transactions can lead to excessive and unwanted blocking as well as increased overhead.

Log Flushes/sec

This performance counter returns the number of log flushes per second, averaged since the last collection time.

A log flush happens when a transaction is committed and data is written from the log cache to the transaction log file. The log cache is a location in memory where SQL Server stores data to be written to the log file. This is used to roll back a transaction before it is committed. Once complete, the log cache is flushed to the physical log file on the disk. Generally speaking, log flushes per second should parallel the number of transactions per second.

Potential Issue: If the returned value is higher than expected, check your use of explicit transactions in your queries.

Resolution: Explicitly define the start and end of your transactions. This should reduce the number of log flushes, and reduce the impact on I/O. Also check the Log Bytes Flushed/Sec monitor.

Log Flush Waits/sec

This performance counter returns the number of commits, per second, waiting for the log flush.

This value should be as low as possible.

Potential Issue: A high number of Log Flush Waits can indicate that it is taking longer than normal to flush the transaction log cache. This slows the performance of your SQL Server.

Resolution: Check the value of the Disk avg. Write time monitor. If the returned value is greater than 5ms, then this can indicate that there is an I/O bottleneck. Move your transaction log file to a disk drive separate from your data files. This should increase performance since there is no access competition on the same disk. Consider upgrading to RAID 10 for transaction log storage. Also, adding spindles to your RAID array should increase performance.

Log Flush Wait Time

This performance counter returns the total wait time (in milliseconds) to flush the log to the transaction log file.

On an AlwaysOn secondary database, this value indicates the wait time for log records to be hardened to disk.

Percent Log Used

This performance counter returns the percentage of space in the log that is in use.

This is the size of the transaction log that actively contains data in relation to the total physical size on disk of the log file.

Potential Issue: If the log file is 100% used, it attempts to increase its size. If there is not sufficient disk space to accommodate the growth, the database stops functioning.

Resolution: Perform a transaction log backup to truncate the log.