1

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!

Improve this question

1 Answer 1

Reset to default
0

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'
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .