SQL Server Full-Text Index rebuild causing latency on other indexes

Question

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?

Answer 1

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.

Idle CPU

Defining when a CPU idle condition occurs is done in the properites of the SQL Server Agent.

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

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.

%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG

2012-07-13 04:00:04.62 spid105s Informational: Full-text Full population initialized for table or indexed view '[database_name].[schema_name].[table_name]' (table or indexed view ID '66099276', database ID '13'). Population sub-tasks: 4.

2012-07-13 04:04:14.59 spid131s Informational: Full-text Full population completed for table or indexed view '[database_name].[schema_name].[table_name]' (table or indexed view ID '1621580815', database ID '13'). Number of documents processed: 101. Number of documents failed: 0. Number of documents that will be retried: 0.