Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
george.pancescu
Starting Member
1 Post |
 Posted - 2012-01-16 : 06:21:59
|
| Hello,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] rebuildupdate 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! |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-01-18 : 12:10:31
|
| If you changed it to a reorg instead of rebuild would the CPU problem still happen? For reasons that differ from yours I switched to using reorgs at the advice of another member here. Maybe worth a try? |
 |
|
|
|
|
|
FTS - Index rebuild causing CPU issues