Sql-server – Why is full text index falling behind

full-text-searchindexsql serversql-server-2012

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.

Best Answer

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.

Related Question