I am rebuilding a full-text index on a table with a large number of rows. The rebuild seems to be using all of the resources of the FT GATHERER threads, causing all of the other full-text indexes on the instance to report large latency. Is there some way to throttle the resources that the rebuild is using so that the other indexes can keep up?
migrated from stackoverflow.com Dec 3 '12 at 20:02
This question came from our site for professional and enthusiast programmers.
Using swasheck's comment as a potential answer, you can define a job to run the fulltext rebuild whenever the CPU becomes idle.
I also included the location of the crawl logs that SQL Server maintains everytime a catalog is populated.
Defining when a CPU idle condition occurs is done in the properites of the SQL Server Agent.
Using perfmon to identify an inactive timeframe
Another option would be to use Perfmon's Data Collector. You can use the SQL Server General Statistics Login counter to identify a timeframe when the server is less active. You can then define your Full Text Rebuild to run during that timeframe.
I didn't notice any particular way to limit the population or rebuild to one cpu with MAXDOP, like you can with rebuilding indexes.
ALTER FULLTEXT INDEX ON database_name.schema_name.table_name START FULL POPULATION; http://msdn.microsoft.com/en-us/library/ms188359.aspx
ALTER FULLTEXT CATALOG catalog_name REBUILD; http://msdn.microsoft.com/en-us/library/ms176095.aspx
Checking the FT Logs
There are logs that monitor the population of full text catalogs. These can be used to identify problem areas and also give you an idea of how long each catalog is taking to populate.