CPU Usage vs Catalog Population

I have an SQL account that uses FTS on SQL 2000.
I originally used Incremental Population to index the work done during the
day.
However, the indexing process was carrying over into the next work day.
Therefore, last week I changed the method to Change Tracking with Update
Index in Background (with John Kane's urging) to try to alleviate the loss
of performance being experienced during the day by the system's users.
It is my understanding that CT w/UIB would only perform indexing when the
CPU is idle and resources are available. By switching to this method, I
thought the overall performance would improve.
This morning I checked the SQL Server, CPU usage is averaging 60% and the
catalog status reports Population in Progress.
Consequently, all users are unable to perform logins, searches, ... because
of timeouts against the database.
I am remote to the application and finally got the menu up to issue the
command to stop population.
Q1:
I thought the Change Tracking with Update Index in Background option is to
prevent high CPU utilization.
Should I be seeing such high utilization with this option selected to the
point that other applications are timing out when trying to make a
connection to the database? Enterprise manager also times out when trying to
connect.
Q2:
The stop command has finally executed and the catalog status now reports
idle.
It has been over an hour and the CPU usage is still very high (50 - 60%).
Is this continued usage the result of the indexing process?
Is there residual processing that could go on for hours after the status
reports idle?
Q3:
Related to Q1, since the population status reports idle but I still see high
CPU utilization, how do I isolate what in particular is keeping the usage so
high? How to prove it is mssearch and not something else going on with the
database?
I see the sqlserver and mssearch processes listed in task mgr with high cpu
& memory usage.
Update index in background does not necessarily index when the CPU is idle
or resources are available. Rather it polls a table every second and indexed
rows which are marked to be re-indexed.
You might want to try a scheduled Update Index at intervals that work for
your requirements. For instance update the index every 1/2 hour.
If your table you are indexing is heavily updated you may run into these
problems with relatively high CPU utilization, you will also run into these
problems if your content is in German or Far East languages.
High CPU utilization just means your processor is busy, it isn't by itself a
bad thing. When it starts to cause execessive paging or create queueing is
when it can degrade overall system performance.
To isolate particular processes using high cpu you can use Performance
Monitor or simply sort the processes you find in Task Manager by CPU. You
can also run sp_who2 to get an idea of cumulative CPU and disk IO.
"RGondzur" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:es8K7UqFEHA.3336@.TK2MSFTNGP12.phx.gbl...
> I have an SQL account that uses FTS on SQL 2000.
> I originally used Incremental Population to index the work done during the
> day.
> However, the indexing process was carrying over into the next work day.
> Therefore, last week I changed the method to Change Tracking with Update
> Index in Background (with John Kane's urging) to try to alleviate the loss
> of performance being experienced during the day by the system's users.
> It is my understanding that CT w/UIB would only perform indexing when the
> CPU is idle and resources are available. By switching to this method, I
> thought the overall performance would improve.
> This morning I checked the SQL Server, CPU usage is averaging 60% and the
> catalog status reports Population in Progress.
> Consequently, all users are unable to perform logins, searches, ...
because
> of timeouts against the database.
> I am remote to the application and finally got the menu up to issue the
> command to stop population.
>
> Q1:
> I thought the Change Tracking with Update Index in Background option is to
> prevent high CPU utilization.
> Should I be seeing such high utilization with this option selected to the
> point that other applications are timing out when trying to make a
> connection to the database? Enterprise manager also times out when trying
to
> connect.
>
> Q2:
> The stop command has finally executed and the catalog status now reports
> idle.
> It has been over an hour and the CPU usage is still very high (50 - 60%).
> Is this continued usage the result of the indexing process?
> Is there residual processing that could go on for hours after the status
> reports idle?
>
> Q3:
> Related to Q1, since the population status reports idle but I still see
high
> CPU utilization, how do I isolate what in particular is keeping the usage
so
> high? How to prove it is mssearch and not something else going on with the
> database?
> I see the sqlserver and mssearch processes listed in task mgr with high
cpu
> & memory usage.
>
>