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 backup and transaction log truncate

Hi,
I'm about how the backup system works in SQL Server. I'm using
the BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be by the statement in BOL I actually hope
that BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope that
> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:

> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope
> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>