Measure transaction log throughput?

Question

After reading Kim Tripp's article on transaction log throughput and discovering that I have gazillions VLF, I plan to restructure the logs as she emphasized. I want to measure the resulting increase in log bandwidth to see if fragmentation affects my servers, but I don’t understand how to do this. I could not find anything in BOL or Google to measure log throughput, and the best strategy I could put together was to see if the average latency for each task was reduced for LOGBUFFER and WRITELOG.

SELECT wait_type, (wait_time_ms - signal_wait_time_ms) * 1. / 
       waiting_tasks_count AS [Wait (ms) per Task]
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('LOGBUFFER', 'WRITELOG')

Is there anything more definitive, perhaps similar to perfmon database bandwidth counters ( http://technet.microsoft.com/en-us/library/ms189883.aspx )?

Answer 1

select * from sys.dm_os_performance_counters
where counter_name in ('Log Flushes/sec'
  ,'Log Bytes Flushed/sec'
  ,'Log Flush Waits/sec'
  ,'Log Flush Wait Time')
and instance_name = '<dbname>';  

This is a performance counter, you will need to calculate the actual value from the raw value. For the Log Timeout Counter counter, which is of type 65792 (that is, NumberOfItems64 ), it’s simple: the raw value is the value. But others are of type 272696576 (i.e. RateOfCountsPerSecond64 ), for which the value is calculated by dividing the delta or two consecutive raw values ​​by the number of seconds elapsed between gluing the samples.

easieer, Perfmon.exec .