How to refresh Full Text Indexes?

Hi All,

i am using full text search in stored procedure for my web application search-engine.

how do i refresh my fulltext indexes which i created using unique indexes?

as i can see two options tart 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? i found, when i run query first time with contains keyword , it takes much time.

what enterprise settings required to increase performance?

Thanks in advance.

When you start a population of a fulltext catalog, it reports successful but that only means its 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 realtime 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.

HTH!

|||

Hello,

thanks a lot for the reply.

can you please verify the way how i created Index?

create fulltext catalog MyFullTextCatalog

create unique index ui_AssetID on EMAM_Assets(ID)

create fulltext index on EMAM_Assets(title,asset_desc) KEY INDEX ui_AssetID on MyFullTextCatalog
with CHANGE_TRACKING AUTO

i created full text index as mentioned above. i want to perform search on title and asset_desc.

I created one schedule (daily) on table -> full text properties to populate full text index. I didnt get your point about timestamp. where do i need to add and how to use it? where will it be helpful?

second, i click on Full text Catalog property, its showing error: Property FullTextIndexSize is not available for FulltextCatalog. This property may not exist or may not be retrieved. once i click on OK on this error alert, its opening Full Text Catalog Property. Is it something wrong?

Please guide me in set up Administration configuration for Full Text Index. I am working on Search Engine and Query performance is very much important. i having 5 tables on which i need to perform search.

|||

You need to add a new column to the table you are running incremental populations on with a datatype of TIMESTAMP, the value of which is controlled by SQL Server to keep track of changes to records in the table.

With the commands you have used, SQL Server should populate the catalog as soon as the index is created. Rather than using the GUI to check the property, use FULLTEXTCATALOGPROPERTY in a query window (see Books Online for more details). The error you get may be related to the GUI rather than your full text catalog.

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

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx

HTH!

|||

Hi,

thanks for valuable input.

I created full text index with Change Track Automatically.

As i understand, in that case i need not to worry about full text population. is that right?

Full text index refresh will be done automatically. right?

let me know if it is required in this case also.

i already removed schedule for full text index population.

now i am facing on big problem is that: Query taking around 1 min while executing first time.

after that if u will be sit ideal for around 20 min then once again query will take more time.

instant execution followed by first one will result very fast.

I will check for cache memory and I/O for that. i think it could be reason.

Please let me know if you have any idea.

Thanks.

|||

You're right- once you've enabled change tracking a Full Population should happen automatically and the index will be maintained by sql server.

Make sure that the initial full population of the catalog has completed before running a query as it will be quite a resource intensive process and may be the cause of your slow query,

Good luck!