Post reply

Full Text Indexing

  • May 15, 2006 at 6:37 am

    #91279

    Hi,

    Doe anyone have much experience with Full Text Indexing (FTI). I had a table that was set up with FTI. However on it's last populating process it seemed to go and on. I have removed the catalog and the table that was indexed (about 1 million rows). Now when I try and add back the catalog.. it is taking forever and the process in Current Activity is showing as runnable but with no open transactions.

    Anyone got any thoughts on this?

    Any input would be greatly appreciated.

    Regards..Graeme

  • May 16, 2006 at 8:14 am

    #638161

    The only thing I could recommend is making sure the table has a column of data type "timestamp". It's used by the full text catalog for doing an incremental update of the catalog. Specifically note that data type timestamp is NOT a datetime field. A little work with google will show some specifics.

    My experience has been that populating a full text catalog takes a lot longer when users are using the catalog. For this reason, it's best that full populations of the catalog take place during slow times (say, starting at midnight or so).

    Here's a "best practices" short list for full text catalogs:

    1. Have the ftdata folder on a seperate drive from the databases (example: drive c is OS and live databases, drive d is full text catalogs and backups).
    2. Make sure the tables using full text searching have a "timestamp" column.
    3. Use incremental updates with "update in background" to handle the full text catalog and keep it up-to-date.
    4. Don't start a full population during business/busy hours if you can help it. I only use a full population to get things started when setting up a new database, then use incremental updates to do the rest.
    5. Make sure the real-time antivirus scan is not scanning the following: the ftdata folder, sql backups, and live databases. Ok, this tip isn't specific to full text catalogs, but it is a way to get more performance from sql server. It's ok to scan these, but using the real-time scan hurts performance a fair amount.

    Of course, your environment may vary and you may have different needs, but these should get you started. I found many of these tips on this site and used google to expand on them.

    Good luck!

    James

  • May 16, 2006 at 8:36 am

    #638173

    Hey James,

    Thanks for your input.

    I shall certainly take your comments into consideration.

    We actually have to do a full population each night because the table is totally rebuilt.

    Thanks again

    Graeme

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply