Sql-server – sys.dm_hadr_database_replica_states, understanding the different *_time columns

availability-groupssql serversql server 2014

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?

Best Answer

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.

Related Question