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.