SQL Server fulltext index auto population?

Question

5.00/5 (1 vote)

See more:

I have a table with a full-text index that is essentially a denormalized view of several tables that is automatically updated when any of the base tables are updated via triggers. This table is primarily used for a nightly operation, but I would like to use it for more real-time things if I could only get the population figured out.

When setting up the full-text index, I set 'track changes' to 'Automatic' as I didn't want have to fully populate the index every night. However, it doesn't seem to incorporate newly added records for some time, if at all. I can only get new records to return in FREETEXTTABLE query by re-building the index.

I tried setting up a population schedule twice a day, but that doesn't seem to do anything. I am testing it by looking at newly inserted records in the base tables and searching for them in a FREETEXTTABLE query, expecting the correct ID to show up in the search results.

When researching online, I kept seeing 'populate in background' as a potential solution, but I never saw anything about how to actually enable this.

Any ideas?

Posted 16-Aug-11 6:42am

Zach Swanson

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-08-16T18:35:00

Solution 1

Most likely the background population you saw is another term for tracking changes.

If you add large amount of data in your nightly operations, I would suggest that you drop the index before nightly operations, do the operations and then create the index again (so not using rebuild). When creating the index again, put the change tracking to auto so the index should keep track on the day time operations.

Posted 16-Aug-11 18:35pm

Wendelius

Comments

Zach Swanson 17-Aug-11 10:27am

Thanks for your help Mika. The data operations are performed during the day using a web interface. When making changes, updates to the base tables are automatically copied to the fulltext table (denormalized view) via triggers updating only the affected rows and fields.

How is dropping and re-creating the index different from a full re-population? Ideally, I am wanting the index to be up-to-date within 4 hours of changes made.

Wendelius 17-Aug-11 12:26pm

From your post I understood that you 'massively' updating the data at night time. If that's the case then dropping the index and creating it after the data operations would have 2 effects:
- the speed of data load would be most likely higher (no index maintenance)
- index would be up-to-date after creating it with 'fresh' data

I've seen some situations where change tracking is ignoring some changes which eventually causes missing entries. This has happened if the table containing the index is modified very fast. That's another reason why I suggested not having the index when data is loaded. But as said these apply only if you have a 'load period' for the table

If you allow the index to be few hours late, one possibility is that you don't use background population but create a job under SQL Server agent. In this job you either populate the index manually (ALTER FULLTEXT INDEX...) or rebuild it. You can schedule this job to execute for example every 4 hours.