Sql-server – SQL Server 2008R2 Full Text Catalog Missing Records

full-text-searchsql-server-2008-r2

I create one simple Full Text Catalog on a varchar(500) filed, which mainly store address text like 24 King Street Melbourne VIC 3000. The catalog is set to auto track changes. But I found there are always a few records in the table are not indexed by Full Text Catalog after I fully populate it.

For example, the table has 10,000 records, but the full text has only 9,997 records. So there are three records are not indexed. I rebuild the full text and all records are indexed. But after user start to insert data, there are missing records again. Table records increased to 11,000. But the full text records only have 10,999. Any idea why full text always missing some records?

If I run a manual update on the missing record, it will automatically indexed.

The filed included in the full text is updated by a trigger. It seems full text has issues tracking field that are updated by trigger?

Environment

SQL Server 2008 R2 Service Pack 2 – 10.50.4000

Full Text is set to Auto Track Changes

Best Answer

Full-Text Catalog Indexing takes time.

The official Microsoft Document states (emphasis by me) in the section Population based on change tracking:

By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.

Reference: Populate Full-Text Indexes (Microsoft Docs)

You might want to consider using Incremental population based on timestamp which is mentioned in the same document.

An incremental population is an alternative mechanism for manually populating a full-text index. If a table experiences a high volume of inserts, using incremental population can be more efficient that using manual population.+ You can run an incremental population for a full-text index that has CHANGE_TRACKING set to MANUAL or OFF.
The requirement for incremental population is that the indexed table must have a column of the timestamp data type. If a timestamp column does not exist, incremental population cannot be performed.
SQL Server uses the timestamp column to identify rows that have changed since the last population. The incremental population then updates the full-text index for rows added, deleted, or modified after the last population, or while the last population was in progress. At the end of a population, the Full-Text Engine records a new timestamp value. This value is the largest timestamp value that SQL Gatherer has found. This value will be used when the next incremental population starts.

Related Question