Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and updates statistics like this:

alter fulltext catalog [ftCatalogName] rebuild update statistics [tableNameWithFtIndex]

The job runs weekly, Monday at 9AM; it finishes in around 1minute and always succeeds;

Now the problem: everytime after the job finishes, the CPU of sqlserver.exe process goes up to 99%; regularly the CPU stays at around 70-80% (i know it is very high and we are trying to improve this); and the only way to make it go back to it's regular state (70-80%) is only if we stop the IIS on all front-end servers (we have a webfarm) and then start them up again - this way the CPU for sqlserver.exe goes to 0% when the IISs are stopped, and back to 70-80% when the IISs are restarted back up;

Also i've analyzed the behavior of CPU during the rebuild process: - start FTS rebuild job (above statements) - sqlserver.exe CPU=75% (normal); fdhost.exe=20-25% (normal during rebuild) - job finishes in 51 secs and fdhost CPU goes to 0%, and sqlserver.exe goes to 99% and stays like this until i apply the flow described above (start/stop IIS on front-end servers)

Does anyone have any idea about why this happens? At least an explication... or some ways to optimize this index and make it less resource hungry at rebuild...

Thank you in advance!

share|improve this question
add comment

1 Answer

Your statement just TRIGGERS the rebuild job, not DOING it. So - you triggered the job by ALTER FULLTEXT statement, now you can wait until it completes like that:

WHILE (SELECT COUNT(*) FROM sys.fulltext_catalogs WHERE FULLTEXTCATALOGPROPERTY(name, 'PopulateStatus') IN (1, 6, 7, 9)) > 0
    WAITFOR DELAY '0:00:00.1'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.