One of my clients to whom I helped in configuring Always On Availability Groups came back to me with an interesting situation. They have observed blocking of reading queries on the secondary replica. Since the database is in read-only mode, they wanted to know how write is being performed in the database which is causing blocking?
I knew that this is not a user write activity but must be a system write activity which is causing blocking. When I started troubleshooting, I found below.
- DB STARTUP thread (redo thread) being blocked by user session in sys.dm_exec_requests
- Wait type: LCK_M_SCH_M
- Wait_resource: METADATA: database_id = 8 COMPRESSED_FRAGMENT(object_id = 484196875, fragment_id = 9715700) – found using sys.all_objects
When I looked further, I found the object name was ifts_comp_fragment_484196875_10739738 and it was an INTERNAL_TABLE.
It became clear that the redo thread was getting blocked not a user session. This causes the replica to start lagging because redo stops often. In my lab, I also observed that if a database with a full-text index is in an availability group, we can see the same type of blocking whenever the full text is index is enabled for automatic or manual population, and if there are read queries running full-text searches.
For my client, we were able to prevent this behavior by disabling change tracking. My client was OK with disabling change tracking on the full-text index temporarily and then setting up an incremental population on a schedule. Here is the T-SQL to change the tracking to manual.
USE [CRM] GO ALTER FULLTEXT INDEX ON [dbo].[CustomerData] SET CHANGE_TRACKING = MANUAL GO
Later I suggested my client to refer Populate Full-Text Indexes and think about “Incremental population based on a timestamp”. This was a long-term solution for them.
Reference: Pinal Dave (https://blog.SQLAuthority.com)