Why is full text index falling behind?

Question

I have a full-text index set up on a table; the table is under fairly constant load, receiving ~50k inserts and ~35k deletes daily, with rather short (up to 5 minutes) gaps between inserts.
The index is configured for automatic change tracking, and is usually able to process all documents within seconds from insertion. However, yesterday monitoring alerted that the index was not updated for more than 4 hours.
After receiving that alert, I checked the following:

• No new entries were appearing in full text log. The last entry was informational - full-text auto population completed.
• Index population status (as reported by sys.dm_fts_index_population) was stuck on "Has stopped processing".
• A single session running command "FT BATCH CMPLETE" was using up one entire CPU core. That session's last_request_start_time was within seconds of the last entry in full text log. Other than that the CPUs were idle.
• Two (out of 30) fragments had status 6 / Being used for merge input and ready for query (as reported by sys.fulltext_index_fragments); the size of these fragments was close to 60GB each.

After roughly 10 hours, indexing resumed just as mysteriously as it stopped.

Am I right in thinking that indexing was paused because of the merge? If not, what else can I check in order to get a better diagnosis?

The issue occurs roughly twice a month on different servers. As far as I can tell, the index is not reorganized or rebuilt on a schedule. I'm looking for a solution that would allow me to either avoid indexing pauses completely, or schedule them during maintenance downtime. The server is SQL Server 2012 Enterprise.

Answer 1

There seems to be a few queries you can try to get more information out of what is happening. The below query that was pulled from Stackoverflow takes advantage of FULLTEXTCATALOGPROPERTY.

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'FTS_Demo_Catalog'

SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs AS cat

However, In this article, microsoft mentions

it is usually a better option to check the corresponding PopulateStatus property at the table level, TableFullTextPopulateStatus in the OBJECTPROPERTYEX system function. This and other new full-text properties in OBJECTPROPERTYEX provide more granular information about full-text indexing tables.

To this, I would use the following script:

DECLARE @TableId VARCHAR(MAX)
SET @TableId = OBJECT_ID('gGastroversion')

Select CASE OBJECTPROPERTYEX ( @TableId , 'TableFullTextPopulateStatus' )
            WHEN 0 THEN 'Idle'
            WHEN 1 THEN 'Full Population In Progress'
            WHEN 2 THEN 'Full population is in progress.'
            WHEN 3 THEN 'Propagation of tracked changes is in progress.'
            WHEN 4 THEN 'Background update index is in progress, such as autochange tracking.'
            WHEN 5 THEN 'Full-text indexing is throttled or paused.'
            WHEN 6 THEN 'An error has occurred. Examine the crawl log for details'
        END

Notice, that if you have number 6 as a result it will tell you to check the crawl log. Troubleshooting this information can be found here.

Answer 2

Apparently MS SQL Server 2012 cannot run multiple indexing threads in parallel. Since indexing thread may run a merge after it finishes indexing the current batch of documents, indexing pauses unavoidable.

Manual change tracking does not help. Multiple UPDATE POPULATION commands run on separate sessions also block each other when merge is running.

It is possible to control the size of fragments being merged, for example by reorganizing the index. Reorganize operation merges all index fragments into one. The fragment created by reorganize is too large to be eligible for merging, and new fragments are reasonably small, so merging them is fast.

In our case it takes a few weeks before new index fragments build up to multiple gigabytes, and weekly reorganization is an option.