0

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!

1
  • Compiling execution plans is CPU bound and updating stats means that all of the affected plans will need to be recompiled next time they are executed. Maybe try and figure out whether that is responsible. Though doesn't explain why it would stay in that state indefinitely or not go back to it after IIS restarted. Jan 16, 2012 at 11:32

1 Answer 1

2

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.