viernes, 27 de enero de 2012 12:08
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.
Todas las respuestas
viernes, 27 de enero de 2012 13:04
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.
- Marcado como respuesta shalabhgupta domingo, 20 de mayo de 2012 3:44
viernes, 27 de enero de 2012 16:08
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:
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?