Solved high CPU issue but a little confused | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Solved high CPU issue but a little confusedFor about 4 weeks we were terrorized by CPU flatlining at 70% and making server unresponsive until we either restart SQL service or, believe it or not increasing MAX Server memory for SQL by 1gb. Our server is 2 xeons (8 cores), 50GB ram, Windows 2008, SQL 2005 Ent, Max memory allowed 34 gb
Very powerful machine completely dying. After service is restarted it would work for about 4 days and then CPU would creep up to 70% and server would die again. We do 80% writing and 20% reading, we write about 8 mil records daily to multiple tables. We tried update statistics for the database every hour which helped a little by having server running ay 60% CPU. Long story short here’s what solved the issue:
Rebuild indexes and update statistics job scheduled to work every hour. Server is now working at 20%-30% CPU which is still high but something we can leave with.
Can anyone here explain sientifically why hourly rebuild and sp_updatestats right after solved our issue, and does it make sense at all that it did? Is there anything else we can try doing?
One thing we notice is that our E drive(where Logs are is running at 90% utilization)
Just as a side note: It is very fortunate that we are on enterprise edition and could rebuild indexes Online, if we were not we’d be completely screwed doing rebuild every hour. There must be something we are missing.
Thats good you are able to find the solution…appreciate the solution updaet too.
There is another thread on this. Please followup there.
How are your fill factors set up? with such high inserts I hope they are not tight. That would cause what we have with ya.
… its a long debate ongoing in regard to best setting.. thats the reason I state .. IT DEPENDS