Sql-server – Why SELECT query is waiting on HADR_SYNC_COMMIT

availability-groupssql serversql-server-2016

We have two AlwaysOn nodes in sync commit.
Occasionally SELECT queries are waiting on HADR_SYNC_COMMIT.
What is the underlying process that causes it?

  • SQL Server 2016 SP1 CU5.
  • client sessions are using READ COMMITTED isolation level
  • Allow Snapshot Isolation and Is Read Committed Snapshot On are set to True. But snapshot isolation is not being used because client is enforcing READ COMMITTED.

Query example from primary replica:
select * from dbo.photoFileMetaData where FK_mediaId=@P0
Wait info: (780ms)HADR_SYNC_COMMIT
Status: suspended

Since SELECTs are not updating data, why would they need to wait on a replica sync commit?

Update 2018-08-30:
I was able to capture blocking when it was happening. Here is what is in blocking resource:
metadatalock subresource=QDS_INTERVAL_STABILITY classid=qds_interval dbid=7 lockPartition=0 id=lock1e78c13d580 mode=X

Best Answer

Since the HADR_SYNC_COMMIT wait indicates that the primary is waiting for a log block to be hardened on the secondary, it doesn't really make sense for a plain select query to cause this wait to accumulate - since a select query does not generate any transaction log to be sent.

How are you gathering that wait info? The most likely reason for that wait is that there are inserts, updates, or deletes being executed, and then the select runs within the same transaction, and however the waits are being accounted are including the previously accrued HADR_SYNC_COMMIT waits alongside that select statement.

If the select statement were really running on it's own outside of a transaction, and it's trying to read data that's part of an active transaction (that's waiting on HADR_SYNC_COMMIT):

  • without RCSI, I'd expect to see LCK waits for the select query
  • with RCSI (your scenario), I'd expect the select to just work (since writers shouldn't block readers in this case)

Quick point of clarification: since you have Read Committed Snapshot enabled and turned on, any queries using the READ COMMITTED isolation level are [automatically using RCSI. This is just how RCSI works:

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
...
If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data...

You mentioned that the client sending the queries is enforcing READ COMMITTED. It could be that it's forcing the use of shared locks vs snapshots via a READCOMMITTEDLOCK table hint. But I still thought I'd clarify for completeness.

Related Question