SQL Server Performance

Solved high CPU issue but a little confused

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Michael Kansky, Aug 31, 2011.

  1. Michael Kansky New Member

    For 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)
  2. Michael Kansky New Member

    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.
  3. satya Moderator

    Thats good you are able to find the solution...appreciate the solution updaet too. :)
  4. preethi Member

  5. MichaelB Member

    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.
  6. satya Moderator

    ... its a long debate ongoing in regard to best setting.. thats the reason I state .. IT DEPENDS :)

Share This Page