AlwaysOn Availability Groups log_send_rate

Question

On all our AlwaysOn setups, running Windows 2012 and SQL Server 2012 in virtual machines and on bare-metal, I find that the log_send_rate in sys.dm_hadr_database_replica_states is consistently returning incorrect values.

For example (for synchronous mode)

sys.dm_hadr_database_replica_states.log_send_rate (ave = 36,571 (kb/s listed in bol))

Perfmon - SQLServer:Availability Replica - Bytes Sent to Replica/sec (max = 486,000.000, avg = 259,000.000)

Perfmon - SQLServer:Databases - Log Bytes Flushed/sec (max = 653,044.000, avg = 341,000.000)

I've not seen any posts about this but it doesn't appear to be working correctly. A correct log_send_rate value is useful for monitoring AlwaysOn.

Has anyone else experienced this?

Answer 1

Yes, this was fixed recently in Service Pack 2, Cumulative Update 3. Here's the KB article: http://support.microsoft.com/kb/3012182

"FIX: Log_Send_Rate column in sys.dm_hadr_database_replica_states cannot reflect the rate accurately in SQL Server 2012"

Answer 2

The reason why the log_send_rate (and redo_rate) is a bit tricky to comprehend and "correlate", especially when we are used to the data transfer over an uninterrupted point of time type of thinking is that these two rates are calculated during active times, not all times.

In other words, the log_send_rate will adjust when there are log blocks being sent, but it will not go down when it is quiet and the primary replica is waiting on log to send. Likewise, the same conversely on secondaries with redo_rate.

Answer 3

I've been looking at the log_send_rate values as part of troubleshooting a latency issue we have in one of our production environments.

I have proposed to Microsoft that their definition of the field is wrong, as mentioned here (http://technet.microsoft.com/en-us/library/ff877972(v=sql.110).aspx). "Rate at which log records are being sent to the secondary databases, in kilobytes (KB)/second."

I think my definition below is better. It is... “The rate at which log records are cleared from the send queue”, and log records can only be cleared from this queue when they have already been hardened on all secondary’s, and that can only happen when they have already been sent and recieved, irrespective of how long it took those records to arrive, and how long it took them to be hardened, and how long it took for the secondary to send the acks back to the primary.

That’s a very different definition, even if they look cosmetically the same. Data can be removed from a local in memory queue (log_send_queue) much faster than it can be sent to the secondary's in another region, country or datacentre.

Nikos

@Thomas (I'm still too noob to add comments here, apologies. If easier I can provide my work email and we can discuss offline, and update here when consensus is reached?) Hi Thomas

Unfortunately, while your point is correct, its not the point at stake. Yes, it is harder to correlate for all the reasons you've described, but its not the issue I'm trying to highlight.

The point is, that the field "log_send_rate" in the DMV is not actually the rate at which log records are sent to the replicas.

More accurately, its the rate at which log records are being removed from the send queue, AFTER they have ALREADY been sent to secondary, hardened at the secondary, and then sent an ack back to the primary. Only then can they be cleared from the primary send queue.

That's a completely different meaning from that listed in link I included in my first post. Its also much easier to see the discrepancy when you are dealing with cross regional (such as London to New York) send rates, rather than send rates from and to the local datacentre.