Full Text computation eats too much CPU

Hi,
we are using fulltext indexes computation on Production Server.
MSSEARCH eats between 20 % and 40% in a regular manner.
It picks up to 40% then goes down to zero but some secondes later will climb
again to 20 and up to 40%
We are using System SP sp_fulltext_catalog on a main table with 2 millions
rows
and one NVarchar(255) column on which the FT Index is based.
We are searching ways to :
(1) limit CPU took by MSSEARCH
(2) allow FT computation only during a BATCH period not involving User
Activity and Perf degradation.
Many Thanks
Jean-Marc,
The CPU usage you are seeing is normal and expected behavior for the
MSSearch service during FT Indexing and some type of FT Search processing.
Specifically, for FT Indexing during the Shadow Merge and after an
Incremental or Full Population's Master Merge.
Does your production server support more than one CPU? If so, you can also
use the following command prompt command on the server to set the MSSearch
service to one CPU:
at <current_time+1_min> /interactive taskmgr.exe
and then use sp_configure to set SQL Server to use the other CPU to avoid
the MSSearch service from affecting your normal SQL Server processing. If
your production server does not support multiple CPU's, you can use the
sp_fulltext_service 'resource_usage' <value> and set <value> to 1
(background) as the default is 3 and the max is 5. This will reduce the
performance level of the MSSearch service and therefore reduce the FT
Indexing and FT Search performance levels.
Regards,
John
"Jean-Marc PUGIN" <jmpugin@.mobileworkers.com> wrote in message
news:u#zYJ6W0EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi,
> we are using fulltext indexes computation on Production Server.
> MSSEARCH eats between 20 % and 40% in a regular manner.
> It picks up to 40% then goes down to zero but some secondes later will
climb
> again to 20 and up to 40%
> We are using System SP sp_fulltext_catalog on a main table with 2
millions
> rows
> and one NVarchar(255) column on which the FT Index is based.
> We are searching ways to :
> (1) limit CPU took by MSSEARCH
> (2) allow FT computation only during a BATCH period not involving User
> Activity and Perf degradation.
> Many Thanks
>
>

Full Text Catalog MSSearch.exe owning CPU cycles (60-80%)

I've got a server that even when it's not being used for any queries is running at 60-80%cpu capacity. I've got full text catalog going on on the sql server and noticed that MSSearch.exe (related to full text search) is taking up almost all my CPU Time.

why is this? and what can I do to stop it?

Does your CPU cycle comes down when you stop your Full text catlog on your server?|||

I believe that is by default as and when the FT catalog population occurs on my server, I see the same.

Also mention the service pack level on SQL Server.

|||

MSSearch.exe is Microsoft Desktop Search. The SQL Server full text search service is msftesql.exe. I don't think your issue is actually related to your full-text catalog in SQL Server.

You can restrict Microsoft Desktop Search to only index certain directories, which can help reduce its overhead in the future once it is done indexing.

Hope this helps,
Steve

|||

We are also having some issues here. First, Commerce 2007 out of the box is setup for 10 Full Text Catalogs. This is not enough for us, but just bumping up the number allowed after the 10 are already created seems to have no effect. I do not get any more full text catalogs even though I am adding lots of new virtual catalogs to the server. I would like to know how to increase this and actually have it take effect.

Second, we bulk load our catalogs, we do not "Import" them. I would like to disable all of the full text indexing and catalog building while I am loading. Would I just use sp_fulltext_table to do this? Is there a Commerce 2007 API that I need to call so that Commerce Server is aware that I do not want this enabled. I then want to turn it back on when I finish my bulk load and let it incrementally populate.

Thanks!

-Vince

|||

I'm sorry I'm on SQL Server 2000 and MSSearch IS used for indexing on that and all previous versions I believe. it changed in sql server 2005, but before it was mssearch.exe.

I've just recently learned of using the sp_fulltext_service built in stored procedure...

check this out for all of you who are finding FTS to be a resouce pig:

http://technet.microsoft.com/en-us/library/ms175058.aspx

syntax used is

sp_fulltext_service 'resource_usage', 2
GO

by default the resource_usage is set to 3... I've turned it down to 1 and when I've done that the ram usage drops from 150,000k to 15,000 and doesn't go over 20,000 really.

also if another process (LIKE SQL SERVER) needs the CPU's and if you've got resource usage down to 1 it will pause indexing until the processor demand is lowered again.

THIS HAS PROVEN VERY HELPFUL!

enjoy!

Full Text Catalog MSSearch.exe owning CPU cycles (60-80%)

I've got a server that even when it's not being used for any queries is running at 60-80%cpu capacity. I've got full text catalog going on on the sql server and noticed that MSSearch.exe (related to full text search) is taking up almost all my CPU Time.

why is this? and what can I do to stop it?

Does your CPU cycle comes down when you stop your Full text catlog on your server?|||

I believe that is by default as and when the FT catalog population occurs on my server, I see the same.

Also mention the service pack level on SQL Server.

|||

MSSearch.exe is Microsoft Desktop Search. The SQL Server full text search service is msftesql.exe. I don't think your issue is actually related to your full-text catalog in SQL Server.

You can restrict Microsoft Desktop Search to only index certain directories, which can help reduce its overhead in the future once it is done indexing.

Hope this helps,
Steve

|||

We are also having some issues here. First, Commerce 2007 out of the box is setup for 10 Full Text Catalogs. This is not enough for us, but just bumping up the number allowed after the 10 are already created seems to have no effect. I do not get any more full text catalogs even though I am adding lots of new virtual catalogs to the server. I would like to know how to increase this and actually have it take effect.

Second, we bulk load our catalogs, we do not "Import" them. I would like to disable all of the full text indexing and catalog building while I am loading. Would I just use sp_fulltext_table to do this? Is there a Commerce 2007 API that I need to call so that Commerce Server is aware that I do not want this enabled. I then want to turn it back on when I finish my bulk load and let it incrementally populate.

Thanks!

-Vince

|||

I'm sorry I'm on SQL Server 2000 and MSSearch IS used for indexing on that and all previous versions I believe. it changed in sql server 2005, but before it was mssearch.exe.

I've just recently learned of using the sp_fulltext_service built in stored procedure...

check this out for all of you who are finding FTS to be a resouce pig:

http://technet.microsoft.com/en-us/library/ms175058.aspx

syntax used is

sp_fulltext_service 'resource_usage', 2
GO

by default the resource_usage is set to 3... I've turned it down to 1 and when I've done that the ram usage drops from 150,000k to 15,000 and doesn't go over 20,000 really.

also if another process (LIKE SQL SERVER) needs the CPU's and if you've got resource usage down to 1 it will pause indexing until the processor demand is lowered again.

THIS HAS PROVEN VERY HELPFUL!

enjoy!

full database backup

Question 1

3 GHZ CPU (Intel pentium 4) single cpu + 2 GB Memory + SCSI HDD

Database size 10 GB - How long will full database backup take if the
backup is writing a file to the hard disk (separate hard disk)

Question 2

during this full backup are users and application able to access the
database

for example
a) select records

b) insert , update, delete records

or is the database backup causing the database to be exclusively
locked up ?

Thanks in advance"newbiegca_sqlsever2000" <newbiegca_sql2000@.sbcglobal.net> wrote in message
news:d11c4aba.0409192309.414e6edb@.posting.google.c om...
> Question 1
> 3 GHZ CPU (Intel pentium 4) single cpu + 2 GB Memory + SCSI HDD
> Database size 10 GB - How long will full database backup take if the
> backup is writing a file to the hard disk (separate hard disk)

No idea - just try it and see. You can backup to multiple files/devices at
once if you need to speed things up, although this will probably only help
if the devices are physically independent drives. See "Using Multiple Media
or Devices" in Books Online.

> Question 2
> during this full backup are users and application able to access the
> database
> for example
> a) select records
> b) insert , update, delete records
> or is the database backup causing the database to be exclusively
> locked up ?
> Thanks in advance

A backup is an online operation, and users can continue to access the
database, including modifying data, while the backup is running (see "Backup
Restrictions" in Books Online). You might also want to review the whole
"Backing Up and Restoring Databases" topic for more detailed information on
backups, recovery models etc.

Simon|||Thanks Simon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!