High CPU over extended time period

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

or - 08 Nov 2005 10:45 GMT

Hello,
We are running SQL 2000 sp4 with full text search, recently we are
getting 100% CPU over extended period of time and the only way to
resolve this is by killing the Mssearch.exe process. (we tried to
restart the service but it hang) the Microsoft search service is
running under local system account.
our system topology is active/passive cluster with NAS disks, 4GB RAM
and 4 CPU for each server.
May be some one can help me???
The issue happen randomly with no specific job running at that time or
any thing, and when it happen the SQL queries are running very very
slow.
Thank you
Orna Kahil

Reply to this Message

John Kane - 09 Nov 2005 06:18 GMT

Orna,
Does the CPU usage drop at some point or does it stay high at all or most
times? If the latter, please re-confirm that the MSSearch service is using
the "system account" (LocalSystem) and if not, change it and restart the
MSSearch service.

If the former, you can set the MSSearch service's to use one CPU and then
use sp_configure to set the affinity of SQL Server to that cpu to avoid cpu
contention during heavy MSSearch CPU usage via the following method - launch
the Tskmgr.exe from the AT command on the multi-proc server where sql server
resides, as follows:

at <current_time+1min> /interactive taskmgr.exe

when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.

Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.

Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

Regards,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hello,
> We are running SQL 2000 sp4 with full text search, recently we are
[quoted text clipped - 10 lines]
> Thank you
> Orna Kahil

Reply to this Message

or - 09 Nov 2005 07:57 GMT

Hello John and thank you for your quick response.
The CPU usage does not drop it stack on 100%. and do not release till
we kill the process.
we can change the configuration so the MSsearch will use only 1 CPU but
I think the problem is different since it hang in the same position and
just using lots of CPU usage.
The MSSearch service is using the "system account" (LocalSystem). I
looked at the Resources you point at is there any thing specific there?
I forgot to mention that it all worked fine and suddenly on day (we did
not change a thing) it started to happen.

Reply to this Message

John Kane - 09 Nov 2005 14:36 GMT

You're welcome, Orna,
What is the exact version of SQL Server that you are using? Could you post
the full output of SELECT @@version ?
Depending upon your answer, you may need to upgrade to a new/higher Service
Pack (SP) level to get a fix for a possible known bug related to MSSearch &
high CPU usage. The @@version info will provide that information.

Additionally, please review your server's Application event log for
"Microsoft Search" and MssCi source events. Especially, the latter (MssCi)
for ALL informational, warnings and error messages as this source event may
indicate what the true problem is and hopefully provide more detail.

Thanks,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hello John and thank you for your quick response.
> The CPU usage does not drop it stack on 100%. and do not release till
[quoted text clipped - 6 lines]
> I forgot to mention that it all worked fine and suddenly on day (we did
> not change a thing) it started to happen.

Reply to this Message

or - 13 Nov 2005 09:37 GMT

Hello John,
Sorry I did not reply sooner .
The data from the SQL @@version is:
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
    May 3 2005 23:18:38
    Copyright (c) 1988-2003 Microsoft Corporation
    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
"
and there are no error from the MssCi only information about merging
once a day.
from the Microsoft search we get all sort of this:"Error: Unable to
complete the requested operation because of either a catastrophic media
failure or a data structure corruption on the disk" and some of "An
error occurred in the resource status monitor for instance <SQL Server
Fulltext>: 800706ba - The RPC server is unavailable. "

The SQL drive is located on NAS disks and recently we have problem with
the Snapshot drive (low disk space) the Snapshot drive is separated
from the SQL drive (S:/ sql and I:/ snapshot)
do you think there is any thing to do with the Mssearch problem?

Reply to this Message

John Kane - 13 Nov 2005 17:57 GMT

No problem, Or,
The errors "Unable to complete the requested operation because of either a
catastrophic media failure or a data structure corruption on the disk" and
"An error occurred in the resource status monitor for instance <SQL Server>
Fulltext>: 800706ba - The RPC server is unavailable" indicate some serious
problem with the Full Text Catalog.

Is the FT Catalog located on the "Snapshot"drive that you are having low
disk space problems? If so, then either free up disk space on this drive or
drop and re-locate the FT Catalog to another local disk drive, but separate
from your SQL drive (S:\ or I:\) as the MSSearch service requires at least
15% of free disk space be available at all times. Additionally, the above
errors indicate serious problems with the existing FT Catalog, so even if
the FT Catalog is not on the "Snapshot" drive, I'd still recommend dropping
and recreating it.

I'd also recommend that you review the FT Deployment white paper and other
FTS related Kb articles at "SQL Server 2000 Full-Text Search Resources and
Links"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

Regards,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hello John,
> Sorry I did not reply sooner .
[quoted text clipped - 16 lines]
> from the SQL drive (S:/ sql and I:/ snapshot)
> do you think there is any thing to do with the Mssearch problem?

Reply to this Message

or - 14 Nov 2005 11:03 GMT

Hi John and Thanks again,
The Catalogs located in the SQL drive which mean there is no space
problem, But I think we will follow your advise and rebuild the
catalog, however this will cause down time to our site so maybe you can
help me with estimating the demage.
We have catalog of about 57,000 rows and each row is 33K (max). how
long will it take to rebuild this catalog?
Thanks
Orna

Reply to this Message

John Kane - 15 Nov 2005 05:03 GMT

You're welcome, Orna,
Depending upon your server's configuration and if you have the FT Catalog
located on a separate drive from the database files, it should take between
1 and 1.5 hours to run to completion...

Hope that helps!
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hi John and Thanks again,
> The Catalogs located in the SQL drive which mean there is no space
[quoted text clipped - 5 lines]
> Thanks
> Orna

Reply to this Message

or - 15 Nov 2005 10:25 GMT

Thank you John for all your help we will rebuild the catalogs and
continue increase our knowledge via the KB you pointed at.
appreciate it
Orna Kahil

Reply to this Message

Or - 24 Jan 2006 14:01 GMT

Hello John,
Well after 3 weeks of quite it happen again, I will summaries our actions
1. Rebuild all catalogs
2. Changed population mode to be change tracking with background index
3. Separate the resources SQL is working on 1 CPU and Mssearch on the other.
4. Fixed the Netapp configuration
5. Looked for errors in all the available logs
And nothing helped suddenly one day , again nothing was done by us the CPU
stabilized and was low this situation continued for aprox 3 week and on this
Sunday again it went high to 100% it did not harm the site functioning
because it is on different CPU but it require manual interference .
the occurrences are not on the same time each day. and we have no claw what
is going on here do you have any suggestion
Thank you
Orna

> You're welcome, Orna,
> Depending upon your server's configuration and if you have the FT Catalog
[quoted text clipped - 12 lines]
> > Thanks
> > Orna

Reply to this Message