3

I am going nuts trying to understand the meaning of the different time columns in the sys.dm_hadr_database_replica_states system table.

We have a three node Always On Availability Group, where two of the nodes are sync and one async. We want to use the async node for reporting and want to find an accurate way to calculate the delay between the primary and async replica.

Below is a snapshot of a query:

select synchronization_state_desc, 
       is_primary_replica, 
       last_sent_time, 
       last_received_time, 
       last_hardened_time, 
       last_redone_time, 
       last_commit_time
from sys.dm_hadr_database_replica_states DRS 
where database_id = db_id('DBname')

enter image description here

Edited the post to narrow down the question

If I need to find the actual delay it takes for the data to appear on async node, which columns do I use?

  • Welcome to DBA.SE and congratulations on a pretty impressive first post. Your question contains multiple sub-questions which might result in your question being closed as too broad. If you had to boil your current situation down into one significant question, which one would it be? e.g. "How can we calculate the delay between the primary and async replica using the sys.dm_hadr_database_replica_states view?". What is the significance of your numbered list at the end? (One item is a statement, two are questions). Please consider reformatting and/or rephrasing your question by editing it. – John aka hot2use Aug 13 '18 at 7:50
  • @hot2use, Thanks for your suggestions, I have edited the post. – user158327 Aug 13 '18 at 9:16
1

For a starter, here's the Microsoft Docs Page

As an aside, I suggest you do some reading into the mechanics of an availability group, purely so you can understand my answer. (Microsoft Docs Page)

As for the answer to your question, as normally in the case with questions around an RDBMS, it depends.

In this case is depends on what you mean by "...for data to appear"

Let's have a look at the columns in your question:

  • last_sent_time,
  • last_received_time,
  • last_hardened_time,
  • last_redone_time,
  • last_commit_time

It is also worth noting that the System DMV you are referring to is at the database level


last_sent_time

This time indicates the last time that the PRIMARY sent a Log Block to the available secondaries. This is the start of the data synchronisation process.

last_received_time

This indicates the last time that the secondary received a log block.

last_hardened_time

This indicates the last time that the secondary cached the recieved log block data to disk.

last_redone_time

This is the time that the last LSN was redone on the target database.

last_commit_time

This is the time of the last commit record was redone and reported back to the primary.


Summary

Of the above, there are various entry-points of the data into the secondary systems.

The data first enters the server into memory at last_received_time

The data first enters the server on disk at last_hardened_time

The data first enters the database data files at last_redone_time

The data first becomes committed and available for reading by queries (outside of strange NOLOCK situations) at last_commit_time

I suspect that the answer to your question is the latter of the 4 concepts. There is a small overhead to the time in this column however, due to the transmission time of the data between the SECONDARY and PRIMARY. This is likely to be unimportant in calculations for determining the speed of data throughput though.

  • Thanks for the reply. I went through the documents and what you explained makes sense, but it does not relate to the values that I am seeing. In my case, if you look at the first row in the screenshot, last_sent_time 2:35:18.190, received_time 2:35:18.190, hardned_time 2:35:17.657. So the hardned time is before the sent and receive time. That would mean that the hardned time is referring to an older log block and not one that is sent or received. Is my understanding correct? if that is correct, is (getdate - last redone time) the best approach to calculate lag? – user158327 Aug 13 '18 at 12:00
  • @user158327 They do not all refer to the same LSN / log blocks. The time is literally just a time - the likelyhood is that for the log block sent at time 2:35:18:190, the data has not yet been cached to disk. As you say, it is referring to an older log block. At a guess thinking about it, without constantly selecting values from this DMV into a separate table, I'm not sure this table alone is enough to get your answer in it's true form. – George.Palacios Aug 13 '18 at 12:49
  • @user158327 This may help you sqlshack.com/measuring-availability-group-synchronization-lag – George.Palacios Aug 13 '18 at 12:57

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.