8

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?

  • Have you considered making a report at connect.microsoft.com ? – Max Vernon Jul 23 '14 at 14:53
  • How is AlwaysON configured - synchronous or async mode ? Is there any replication involved ? – Kin Shah Jul 23 '14 at 14:56
  • Did you mean sys.dm_hadr_database_replica_stats, because the DMV you noted does not contain a log_send_rate column. As well the DMV that reports this shows KBs/sec. It is noted in this TechNet troubleshooting article to compare that value with Performance counter Log Bytes Flushed/sec, is this the one you are referring to? – Shawn Melton Jul 23 '14 at 16:25
  • Thanks for the feedback I have updated the question to be more accurate. I plan to make a report at connect but I first wanted to see if anyone agreed that the number looks wrong. – jonwolds Jul 24 '14 at 14:42
2

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"

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.