Full-text indexes stop populating for 30 minutes in SQL Server

Applies to: SQL Server 2012 EnterpriseSQL Server 2012 DeveloperSQL Server 2012 Standard

Symptoms


Assume that you have Microsoft SQL Server 2012, 2014, 2016 or 2017 installed on a server. Consider the following scenarios:

  • Scenarios 1:
    You have several full-text indexes across more or one databases, and population of these full-text indexes finishes near the same time.
  • Scenarios 2:
    You create a full-text catalog that contains many full-text indexes, and population of these full-text indexes finish near the same time.
  • Scenarios 3:
    You rebuild one or more full-text catalogs in which several of the indexes finish populating at or near the same time.
  • Scenarios 4:
    You manually run Alter Full-Text Catalog Reorganize for a catalog that contains many full-text indexes.

In any of those situations, if you turn on trace flag (TF) 7603 to output the verbose logging for full-text population to the SQL Server error log, you see messages that resemble the following:


Additionally, you see a 30-minute wait for master merge, and the log reports that master merge as aborted:

Cause


Master merge runs automatically at the end of a full or incremental population per index. The master merge process reduces the number of fragments for a full-text index to keep queries utilizing the full-text index from becoming negatively affected by the full-text index performance.

The master merge process uses multiple threads to reduce fragmentation per full-text index. SQL Server throttles the number of concurrent master merges that run at the same time. As soon as the threshold is hit, any full-text index that tries to run a master merge will experience a 30-minute wait delay. The full-text index update will not start during this waiting period. Master merge will resume if one of the following two things happen:

  • When the next successful incremental population completes and successfully starts a master merge.

  • Manually run a master merge by running the following command:

    ALTER FULLTEXT CATALOG catalog_name REORGANIZE

    Note The above two options may still hit the master merge limit depending on the number of master merges running at the time.

Workaround


To work around this, try the following methods:

  • Method 1 (recommended): Limit the number of full-text indexes in the same catalog. Recommend 7 or less. Large tables should be in their own full-text catalog. This is a best practice for performance when you rebuild or reorganize indexes. This method can help when Change_tracking is Auto. 

  • Method 2: Set Change_Tracking to Manual, by using the following command:

    ALTER FULLTEXT INDEX ON table_name set Change_tracking = Manual


    Then, create SQL Server jobs to spread out when incremental populations are run. This results in less overlap when you run master merge following index population.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.