3

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

  • 1
    Full Text indexing takes time. Depending on the amount of data inserted a long time. There normally is a delay between when data is inserted and when it is available in the Full Text Search Catalog. – John aka hot2use Aug 18 '17 at 7:06
2

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.

  • Thanks hot2use. I understand Full Text takes time to index records. New inserted records may not be indexed immediately. But the issues here is some records are not indexed for a few days, which should not happen. The whole table contains around 150K records only. Database server is not that busy. It has plenty of time to index inserted or updated records. It should not take a few days to index new records. I'll keep investigating this issue. Thank for the help. – John Li Aug 19 '17 at 9:05
  • Ok. That wasn't apparent from reading your post. Are there any special characters in the text and what language filter are you using? – John aka hot2use Aug 20 '17 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.