Finding Redo thread blocker in AGs

Tomáš Zíka 256

I'm looking at the Extended events session AlwaysOn_health.
One of the events is lock_redo_blocked and I'm trying to find the blocker.

First of all, I haven't found any documentation for this event. The only info I've found was in sys.dm_xe_object_columns and mentioned some of the columns.

I'm trying to figure out what does the resource_0 refer to in this picture:

the number is a bigint so it rules out object_id (which is int). I also haven't been able to match it to either partition_id or hobt_id.

Is there any way I can find the blocker after the fact?

I don't have data from sys.dm_exec_requests at that time (as advised here: troubleshoot-availability-group-exceeded-rto)

Thank you

Accepted answer

Tomáš Zíka 256 Reputation points

2021-09-16T12:33:55.393+00:00

I have figured it out. When I add an addition Event field resource_description to the lock_redo_blocked Extended event (which is disabled as default)

It will collect additional info that helps me point to the metadata's parent object.
Please sign in to rate this answer.
Erland Sommarskog 113.6K Reputation points MVP

2021-09-16T21:19:58.777+00:00

Good work of finding that field!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Erland Sommarskog 113.6K Reputation points MVP

2021-09-15T22:07:02.3+00:00

Finding the blocking process after the fact is of course impossible. SQL Server does not record that to file or something. You need to set up some tracing to capture that, for instance with the blocked process report.

As for the resource id, did you try sys.alllocation_units?
Please sign in to rate this answer.

1 person found this answer helpful.
Tomáš Zíka 256 Reputation points

2021-09-16T07:08:35.17+00:00

Hi Erland, thank you for the response.

The Blocked process report is set up, but while blocking shows up in PerfMon, the blocked process report comes back empty. I have the threshold set to 10 (minimum is 5), but I'm assuming it doesn't show up internal processes. My best guess is that the auto-stats update has been triggered and the Redo thread on secondaries was blocked when trying to acquire SCH-M.

AlwaysOn_health shows up the lock_redo_blocked - but I found no guide on how to parse a lock of type Metadata.
I have tried allocation_units as well - no match.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver15#locks

AmeliaGu-MSFT 13,986 Reputation points Microsoft Vendor

2021-09-16T09:09:09.657+00:00

Hi TomZika,
In the Session Wizard, it is said that resource_0 is the ID of the locked object, when lock_resource_type is OBJECT. But it did not point out the definition of resource_0 when resource_type is matadata.

Best Regards,
Amelia

Tomáš Zíka 256 Reputation points

2021-09-16T11:10:48.507+00:00

Hi AmeliaGu, yes that's exactly my problem.

The lock structures seem to be the same for both extended events and the DMVs. The documentation I've posted above shows how to map the resource id based on many different resource types (Object, Page, Rid, Key, Hobt, etc.) but not for Metadata (or Application). I have an educated guess that it should be a Statistics update in my case. But I'd like a definitive way how to prove that.

Thank you
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Finding Redo thread blocker in AGs

1 additional answer

Your answer