3

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 RCSI enabled?

Below is what i have captured

  • Lead blocker is some long running SELECT query does not show any specific waittype as particular, lets say SPID 129

  • SPID 129 blocks 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 LCK_M_SCH_M

  • Here comes the problem when this SPID 45 just 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?

Improve this question
1
  • 1
    In your case the session 45 must be a redo task which was doing a schema changing operation (index rebuild, for instance). At the same time the session 129 was running a query which used that index.
    – Denis Rubashkin
    Apr 6, 2021 at 14:58

2 Answers 2

Reset to default
5

Queries against read-only secondary replicas implicitly run in snapshot isolation regardless of the session isolation level or RCSI setting. This avoids blocking due to DML changes. Read-only queries still acquire schema stability locks, which will block DDL operations by redo threads and visa-versa.

SPID 129 blocks 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 LCK_M_SCH_M

In your case, it seems a redo thread is waiting for a schema modification lock but is blocked by a very long-running query/transaction schema stability lock. Other queries are in turn blocked by the redo thread.

Take a look at the SELECT query execution plan and duration of the transaction for remediation.

Improve this answer
7
  • Thank you for your answer. I still have some questions to understand better. When you say SSL will block DDL operations by REDO, i guess you are referring to DDL ones arising from primary replica? Just want to understand why would this specific SELECT query would block the DML operation when so many others running throughout the day? I will check on exec plan and duration to match blocking
    – Newbie-DBA
    Apr 6, 2021 at 15:33
  • @Newbie-DBA, yes, it will DDL from the primary the redo is applying to the secondary. The other queries are apparently using different objects and therefore not affected. Check the wait resource in the sys.dm_exec_requests DMV to identify the specific resource.
    – Dan Guzman
    Apr 6, 2021 at 15:44
  • thanks. If in those select queries i use NOLOCK will it prevent using those schema stability locks or anyway we can avoid these select queries blocking?
    – Newbie-DBA
    Apr 6, 2021 at 16:25
  • @Newbie-DBA, NOLOCK will not avoid schema stability locks. These are needed tot prevent the table from being dropped or altered while the query is running.
    – Dan Guzman
    Apr 6, 2021 at 16:34
  • This morning i am seeing even simple SELECT Queries blocking other SELECT queries with LCK_M_SCH_S locks on other stability. Why is this the case now? Let me know if i need to open this is a separate question?
    – Newbie-DBA
    Apr 7, 2021 at 14:57
2

I found the same issue that was coming from an ALTER INDEX REBUILD activity. I solved moving this activity in another time window.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.