Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
is there not a quieter time in which you can do this process? –  swasheck Dec 3 '12 at 21:10
add comment

migrated from stackoverflow.com Dec 3 '12 at 20:02

This question came from our site for professional and enthusiast programmers.

1 Answer

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

enter image description here

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

enter image description here

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.

enter image description here

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.