Transaction terminated running SELECT on secondary AG group

Question

Using SQL Server 2016, Always-On

Running a simple SELECT on a table within a secondary Availability Group.

select distinct some_column 
from some_table oo (nolock) 
inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column

SSMS eventually sends back this error:

Msg 3948, Level 16, State 2, Line 14061 The transaction was terminated because of the availability replica config/state change or because ghost records are being deleted on the primary and the secondary availability replica that might be needed by queries running under snapshot isolation. Retry the transaction.

The same SELECT with (NOLOCK) works fine on the primary AG.

With or without the NOLOCK it fails most of the time 9/10 and sometimes executes correctly but that's rare. No changes were made to the AG at all.

There are. no changes in the AlwaysOn_Health extended event session.

The low_water_mark_for_ghosts in sys.dm_hadr_database_replica_states is NULL for some and for others there is a value. For those with a value, this is also the primary.

Using the script provided by Nic:

;
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);

The output is:

When the LowWaterMarkSecondaryReplica is NULL, its because that's the instance I'm currently executing this query from.

Answer 1

This is caused by one or two things, much like the error says:

1. A config change was made to the AG
2. The primary restarted
3. The ghost cleanup low watermark was set

The first two are fairly obvious as to why this would be an issue. The 3rd is not.

Since secondary replicas automagically remap read-committed isolation level to the snapshot isolation level a few things need to kick in. One of those things is row versioning, since after all SI is an optimistic concurrency.

Row versioning depends on the version store rows to be available on the secondary. If the low water for ghost cleanup is changed, then the secondary may need to cleanup records that are currently used for queries being satisfied by the version store which have a timestamp lower than the new ghost cleanup low watermark. When this is the case, those versions will be cleaned up and any SI datasets that required them will no longer be valid. That's when this error will surface for the session for that dataset.

select distinct some_column 
from some_table oo (nolock) 
inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column

If this query runs quickly, I don't see why it should error out like that as I doubt your low watermark is changing that rapidly. Thus, I'm going to assume this takes some time to run. Having the appropriate indexes, not using a long transaction, and up-to-date statistics will help with this query. Additionally, the NOLOCK hint is completely ignored on the readable secondary, you may already know that and it may just be code re-use which is fine but I wanted to point it out anyway.

We are considering using read-intent to resolve the issue, do you think that will resolve the problem?

ApplicationIntent is only used for two things in general with AOAGs:

1. read only routing (ROR)
2. to access AG Replicas that are set as secondary mode read-intent only.

This will not solve the underlying problem. IMHO you have two options, make the query run faster (if it isn't already) or open up a support ticket with MS. The reason I say open a ticket is this query should not constantly fail 24/7 and the issue should be looked into.