question

TomZika avatar image
0 Votes"
TomZika asked ErlandSommarskog commented

Finding Redo thread blocker in AGs

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:
132395-image.png

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






sql-server-general
image.png (14.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomZika avatar image
0 Votes"
TomZika answered ErlandSommarskog commented

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)

132670-image.png

It will collect additional info that helps me point to the metadata's parent object.

132758-image.png



image.png (48.8 KiB)
image.png (17.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good work of finding that field!

1 Vote 1 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered TomZika commented

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?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver15#locks

0 Votes 0 ·

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.


132659-image.png

Best Regards,
Amelia


0 Votes 0 ·
image.png (19.4 KiB)
TomZika avatar image TomZika AmeliaGu-msft ·

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

0 Votes 0 ·