Performance Issues with Full Text Index

Question

My Full-Text search takes a long time to perform the search operation. The table on which the Full Text is enabled, contains 97 Lac records and every 1 hour 3 to 4k records are imported to this table.

During the FTS, CPU shows 100% performance on the database server with high CPU consumption of this process: MSFTESQL

The rebuilt on the full text catalog is performed every night. I also tried to do a reorganize every-night but it did'nt helped me.

Could you please guide me what should I do in this case and will Rebuild or Reorganize of FTI , boost performance.

---

shalabh

Answer 1

Hi shalabh,

You need to make it to use all the cores/processors available to the maximum.To use all processors or cores to the maximum, set sp_configure ‘max full-text crawl ranges’ to the number of CPUs on the system. There are many ways to tuning the performance of FTS.

You can refer to below links for performance tuning of FTS.

http://technet.microsoft.com/en-us/library/ms142560.aspx

http://msdn.microsoft.com/en-us/library/aa175787(v=sql.80).aspx

Answer 2

What version of SQL Server are you running?  I suspect you are using SQL Server 2005 since you mention MSFTESQL running. (As of SQL Server 2008 the full text indexing is integral to sqlservr.exe.)

Are you using change tracking for your Full Text index?  If not, then I recommend that you do so using:

   CHANGE_TRACKING AUTO

Aside from some failures or changes made to the definition the catalor or the full text indexes, you should not need to rebuild the full text catalog each night.  Also, with change tracking, the indexing load should be spread across the day in small segments.  Reindexing 9,700,000 rows is a large set of data. 

How big are the text columns or the documents that are included?

In terms of the search time for Full Text searches, how many rows are you getting from each FT Search?  How powerful a computer are you using?   What size result set are you getting?  Can you show a representative query?

RLF