We recently migrated from LogShipping
standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the database in question is over 20 TB and has mix of read write workload on primary.
After moving to new setup of AG we have started seeing blocking which i am not able to understand. Why select queries on secondary are blocking other select queries in my readable secondary replica instance, even when the database being queried has
Below is what i have captured
Lead blocker is some long running
SELECTquery does not show any specific waittype as particular, lets say SPID
SPID 129blocks a session ID
45( i am sure this is not a user id) for almost 6 hours which is dependent on spid129 and wait type is
Here comes the problem when this
SPID 45just blocks all other select queries now in that 6 hour duration.
I am not able to understand what is happening. Can someone help me troubleshoot or look in correct direction?