Why SELECT query is waiting on HADR_SYNC_COMMIT?
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.
Seems like Query Store was the issue here. We have not seen any blocking on SELECT queries after disabling Query Store.
UPDATE The root cause of the issue was the cache refresh process that runs every 15 minutes somewhere in WMI subsystem. This change has been introduced with Windows updates and has been fixed about 6 months after with Windows updates as well.
SQL Server is using specific WMI query (Win32_PerfFormattedData_PerfOS_Memory) to get memory usage on the server. The more memory you have - the more critical issue was.
Query store somehow was involved it.
We did not receive any confirmation from Windows team that this was a bug and it has been fixed. But the fact is - it came and gone with Windows updates. For now we are running Query Store just fine again. However, Windows team said that that particular WMI function has not been changed for a very long time.
Here is an example of slow response time from WMI every 15 minutes which screwed everything up.
