1

I am trying to understand population options for SQl server's full text indexes. My target is to access whether our currently used option (default = CHANGE_TRACKING AUTO) is the best to use in our case.

I think I have understood perfectly every bit of information in the related MS reference page, except from this devilish line from the subchapter Population based on change tracking/Enable change tracking/Automatic population:

...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.

Nothing in the following reference, however, gives any information about what exactly not immediately means in regards to the exact time the population happens. I have found around queries that can return the last timestamp the population took place; but how can I have an idea of how often and when the system tries to, or indeed executes, the population?

-1

concerning "The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index." from what I can tell and experience it means the change to the full-text is updated as any other clustered index, using a background task and not on the immediate instant that the data was changed and the update was triggered.

Also reading a bit more on the documentation (create fulltext index):

"CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] } Specifies whether changes (updates, deletes or inserts) made to table columns that are covered by the full-text index will be propagated by SQL Server to the full-text index. Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."

This is for the manual option meaning that for the AUTO the reverse can be applied, the change is done by the change tracking event and not immediate.

And here is why (Population based on change tracking):

"Optionally, you can use change tracking to maintain a full-text index after its initial full population. There is a small overhead associated with change tracking because SQL Server maintains a table in which it tracks changes to the base table since the last population. When you use change tracking, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts. Data changes made through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."

| improve this answer | |

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.