fulltext search management after implementing it into your database

Hello friends,

Below is question and very good reply about fulltext search management after implementing the same into your database. Please have a take a look.

QUESTION:

Hi All,

How do I refresh my full text indexes which I created using unique indexes as I can see two optionsstart full population and Start Incremental population in full-text index on right click of table. on click of anyone its showing successful.

Can anyone please let me know what is the standard way to refresh full text indexes periodically in terms of best performance?

What enterprise settings required increasing performance?

Thanks in advance.

ANSWER:

When you start a population of a fulltext catalog, it reports successful but that only means it’s started successfully. The population can take a while depending on size of columns, number of rows etc.

Depending on the size of the index and how long it takes to do a full population, you may want to do run regular incremental populations which will only update the records that have been added/changed since the last full population. Population is quite a resource intensive process so be aware of what effect it has on your application. Also remember you'll need to add a timestamp field to any tables which you want to do incremental populations on.

If you need to real-time updates to happen, look into enabling change tracking/background update which will keep your index in sync with the data. Otherwise, new/changed rows won't be reflected in your fulltext index until you do another population.

As with any index, as data gets added and removed the index becomes fragmented so i'd look to schedule a rebuild of the catalog periodically (how frequent is up to you).

The first query may have been slow as the population may still have been in progress. Look at FULLTEXTCATALOGPROPERTY to check the size of the index and the current population status.

Have a look at the following link which has some great tips on Full Text Searching:

http://technet.microsoft.com/hi-in/library/cc917695(en-us).aspx

Happy Knowledge Sharing,