SQL SERVER – Always On Availability Groups and Full-Text Index

One of the most successful offerings from me has been Comprehensive Database Performance Health Check. Sometimes during my assistance, some random issues appear which I try to solve as well. In a recent engagement, one of their developers asked a question about the coexistence of full-text index and always on availability groups. In this blog, we would learn about one common issue which you might face when full text and availability group is used together.

THE PROBLEM

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?

THE INVESTIGATION

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.

1. DB STARTUP thread (redo thread) being blocked by user session in sys.dm_exec_requests
2. Wait type: LCK_M_SCH_M
3. 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.

THE SOLUTION

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)