Advice for sql server full text indexing change tracking and population

Question

I have two tables that have millions of records.
I want to full text search a couple of columns on them (basically first and last names.) However, both of these tables essentially get wiped and replaced by a legacy DTS package over the weekend.
I had change tracking set to automatic and when said DTS ran, the table got locked up and all queries failed.

So, I would like to do this the right way. What settings or scheduling should I use for the FTS change tracking and population? Should I set change tracking to off, and then schedule a full population after the DTS imports run?

Also, what is the difference between "change tracking" and "population" and how are they related?
How can the index work if there is no change tracking?
Does full population add the indexes even if change tracking is off?

Answer 1

There are multiple ways to approach the index population when the tables get wiped over the weekend. One option is to drop the full-text index on the table before wiping it and re-create the index after the legacy DTS package replacement. Another option is to alter the index to set CHANGE TRACKING to MANUAL. After the table has been wiped and re-populated the ft index population can be triggered by calling ALTER FULLTEXT INDEX ... START UPDATE POPULATION. I prefer the latter (don't forget to reset the change tracking to AUTO if this table sees a lot of updates).

Difference between CHANGE_TRACKING and POPULATION -

CHANGE_TRACKING specifies when and if changes made to the table are propagated to the full-text index. It can be set to AUTO, MANUAL or OFF.

POPULATION only matters when CHANGE_TRACKING is set to either MANUAL or OFF.

When set to MANUAL, the user has to trigger the update population command so that full-text can index the tracked changes i.e. they are not indexed automatically.

The OFF setting specifies that SQL Server does not keep track of the changed data, instead indexing the table only once during the command invocation. When NO POPULATION is specified with CHANGE_TRACKING set to OFF, the full-text indexes are left un-populated till the user calls ALTER FULLTEXT INDEX with the START FULLTEXT POPULATION or START INCREMENTAL POPULATION clause.

The CREATE FULLTEXT INDEX MSDN article covers all of these options in more depth.