April 2, 2009 at 4:56 pm
I need some help. I have a full text catalog that has 38 million + rows. It needs to be rebuilt nightly, which takes two and a half hours to run. It is hammering the system (CPU climbs 300%) at this time. This system has users on it 24/7 and the performance is very poor for this timeframe.
Any suggestions on how to run this with little impact to the system?
April 4, 2009 at 9:47 am
Are you rebuilding the entire catalog every night? Is it possible for you use the Change Tracking AUTO feature or do Incremental Population?
--Ramesh
April 4, 2009 at 9:54 am
Yes, we are rebuilding the entire catalog nightly. How do you do increemntal updates to a catalog? This could be an option.
April 4, 2009 at 10:03 am
christian.benvenuto (4/4/2009)
Yes, we are rebuilding the entire catalog nightly. How do you do increemntal updates to a catalog? This could be an option.
Straight from Books Online: (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/76767b20-ef55-49ce-8dc4-e77cb8ff618a.htm)
Incremental timestamp-Based Population
Incremental population updates the full-text index for rows added, deleted, or modified after the last population, or while the last population was in progress. The requirement for incremental population is that the indexed table must have a column of the timestamp data type. If a timestamp column does not exist, incremental population cannot be performed. A request for incremental population on a table without a timestamp column results in a full population operation.
Incremental population requests are implemented as full populations if any metadata that affects the full-text index for the table has changed since the last population. This includes altering any column, index, or full-text index definitions.
At the end of a population, the SQL Gatherer records a new timestamp value. This value is equal to the largest timestamp value that the SQL Gatherer has seen. This value is what will be used when a subsequent incremental population starts.
--Ramesh
April 4, 2009 at 10:10 am
So is this a task that is continuosly running or is it a scheduled task.
Sorry for asking questions I could look up myself, but I'm at home and do not have access to BOL
April 4, 2009 at 10:36 am
Incremental Population is a scheduled task, whereas Change Tracking AUTO is a continuous running process that tracks the tables/indexes activity and relatively populates the full-text indexes.
BTW, here is online MSDN Library link of Books Online: http://msdn.microsoft.com/en-us/library/ms130214(SQL.90).aspx
--Ramesh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply