Gets the status of ghost rows and the low watermark per replica in AGs

GhostRecordStatusPerAGReplica.sql

;
WITH    PrimaryStats
          AS ( SELECT   DB_NAME(database_id) AS DatabaseName ,
                        low_water_mark_for_ghosts ,
                        ar.replica_server_name ,
                        ar.availability_mode_desc
               FROM     sys.dm_hadr_database_replica_states hdrs
                        JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
               WHERE    ar.replica_server_name = @@SERVERNAME
             )
    SELECT  DB_NAME(database_id) AS DatabaseName ,
            hdrs.low_water_mark_for_ghosts AS LowWaterMarkSecondaryReplica,
            ps.low_water_mark_for_ghosts AS LowWaterMarkLocalReplica,
			ps.low_water_mark_for_ghosts - hdrs.low_water_mark_for_ghosts AS GhostWatermarkDiff,
            ar.replica_server_name AS ReplicaNode,
			DATEDIFF(SECOND, last_redone_time, GETDATE()) AS RedoDiffSec,
			last_redone_time
    FROM    sys.dm_hadr_database_replica_states hdrs
            JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
            JOIN PrimaryStats ps ON ps.DatabaseName = DB_NAME(database_id)
    ORDER BY 
            DatabaseName ASC,'NODE ' + right(ar.replica_server_name ,1);