We have two AlwaysOn nodes in sync commit.
Occasionally SELECT queries are waiting on
What is the underlying process that causes it?
- SQL Server 2016 SP1 CU5.
- client sessions are using READ COMMITTED isolation level
Allow Snapshot Isolationand
Is Read Committed Snapshot Onare 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
Since SELECTs are not updating data, why would they need to wait on a replica sync commit?
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