2

Always On availability group with two nodes, synchronous commit.

Redo thread contention on the secondary replica regularly creates a very large redo queue. I have confirmed the wait types are similar to the following:

https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/06/troubleshooting-redo-queue-build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-wait_info-extended-event/

In my case, this is one extended events session(captured when the redo queue was large) output grouped and aggregated like in the above link:

enter image description here

My question:

How can I find out the exact source of the DDL operation which is causing the LCK_M_SCH_M wait?

Improve this question
4
  • 1
    Leaving this as a comment because it's nowhere near fleshed out enough to be an answer. The challenge here is that you need to connect to the primary node in order to get the query, but you don't just want to see waiting queries on the primary - because they could be waiting for anything. You would want to do a join across linked servers, checking DMV contents for blocking queries. You can't join by the spids, obviously. Instead, you're going to need to look at the locks that are being held on both sides.
    – Brent Ozar
    Aug 8, 2017 at 10:50
  • 1
    The cheesy way to do it would be: when the locks happen, simply capture sp_WhoIsActive @get_locks = 1 to a table, on both sides, at the same time. Then later, at your leisure, review the contents of the lock column (XML property bag) to figure out which select query on the secondary is holding a lock that the write query on the primary needs. (If anybody wants to clean these comments up and actually put the right queries in as an answer, kudos to them!)
    – Brent Ozar
    Aug 8, 2017 at 10:51
  • 1
    Can you post your XE trace definition? Are you using the definition from the blog you mentioned?
    – SqlWorldWide
    Aug 8, 2017 at 12:06
  • @SqlWorldWide I am using the exact definition from the blog yes
    – Peter
    Aug 8, 2017 at 12:11

1 Answer 1

Reset to default
1

As Brent Ozar mentioned in the comment section that this is not a simple task to find wait type (and what is causing the wait) between primary and secondary with correlation to time. I am answering your question about finding the source. I modified extended event trace definition given in the blog post you mentioned. Removed the where clause so you can capture all the sessions that is causing wait.

Added few more actions to capture more information. For example:

  • sqlserver.client_hostname
  • sqlserver.plan_handle
  • sqlserver.session_nt_username
  • sqlserver.sql_text

Here is the full definition.

CREATE event session [redo_wait_info] ON server ADD event sqlos.wait_info( action(package0.event_sequence,sqlos.scheduler_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.plan_handle,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) ) ADD target package0.event_file(SET filename=N'C:\Redo_Wait_Info.xel',
  max_file_size=(50), 
  max_rollover_files=(100)) WITH (max_memory=4096 kb, 
event_retention_mode=allow_multiple_event_loss, 
max_dispatch_latency=120 seconds, 
max_event_size=0 kb, 
memory_partition_mode=none, 
track_causality=OFF, 
startup_state=ON)
GO
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.