October 14, 2012 at 2:58 am
GilaMonster (9/29/2012)
One more interesting thing, I started re-indexing job manually, when SQL Server was working OK. Then, after re-indexing job is finished, CPU started to go on 100% again. I was waiting some time, and then restarted SQL Service.
How is possible to CPU go 100% after re-indexing is done?
Why would it not be?
You have queries running your CPU to 100%. Just because you've rebuilt the indexes doesn't mean that badly written queries are suddenly going to behave.
That is opposite of what I said.
I said that queries worked fine during the day. Pages are loading in 2 seconds, and some bigger in 5 seconds. That is perfect.
THEN (after queries worked fine all day), I executed reindex job, and after it's finished CPU usage was 100%.
So, there is nothing wrong with queries. Maybe they could be optimized, but definitely not what is causing 100% CPU usage.
Must check this reindex job, to see what it is doing.
Anyone know for case when reindexing is not successful maybe?
Or good proven script which you use for reindexing?
October 17, 2012 at 2:06 am
I solved the problem. 😀
If anybody ever comes to CPU 100% usage problem, which can't be explained by classic reasons like bad queries, here is one more possible reason that you can check:
There was a job in Agent, that shrinked database log file.
After log was shrinked, it started to grow again.
Since there is another job which does a lot of INSERTs, log grows very fast.
The problem was that that fast growing after shrinking was somehow caused internal fragmentation of log file, and also I suspect that SQL Server needs some time to increase log file size (10% grow by default).
The solution was to remove log shrink job completely and avoid decreasing of log file size.
I switched database recovery mode to Simple because regular backup are enough for our type of business. But, if you need Full mode, just be sure to make log backups regularly.
So, now log file has constant size, about 1GB, and that is its daily load.
After backup is executed, it clears log file but it keeps same file size as reserved space for new logs. On that way, log file internal fragmentation is avoided, and SQL Server doesn't need to constantly increase log file size by 10% (both operations take CPU).
Conclusion: It's best if log file has always same size. 😉
Anyway, CPU usage is about 20% now and everything looks fine. Thanks everybody for help. 😎
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply