SQL Server Performance

High CPU usage - hourly online rebuild + update stats helps

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

  1. Michael Kansky New Member

    During the high loads our SQL server eats 80% of our CPU and eventually becomes unresponsive due to completely corrupted query plans
    The solution was to do hourly online index rebuilds + update stats
    The solution is not something we can be satisfied with because it just does not make any sense.
    Did anyone experience the same issue and is there a solution?
  2. katta New Member

    Hi, We kind of had same problem, to some extent creating of indexes and analysing the profiler trace, helped . Also updating stats also worked. But i guess in your case, you are saying during high loads, see if there are any deadlocks occuring. That could be a possbility. Or there are indexes on the tables where there are heavy inserts,
  3. Michael Kansky New Member

    That's what exactly happens. We have two tables which have 8-20 mil records inserted every day. So i guess hourly index rebuild/reorganize + update stats is the only option to help performance on such a busy DB?
  4. preethi Member

    Isn't hourly rebuild/reorganize an overkill? Is your database getting fragmented/ statistics outdated so fast?
    May be monitoring the data update Vs. statistics updates differences may help to find the interval.
  5. satya Moderator

    I think this is more open question, with additional information we can reduce the issue.
    How can you say corrupted query plans?
    Do you see any warnings or messages in this case?

    What information can you find from the execution plans for these queries that are taking high CPU% on the server?
  6. preethi Member

    I still believe an hourly rebuild is an overkill. You can update stats after loading the data if it happens during a particulrtime of the day. Are you deleting old data from the table?
    Do you see any changes in query plans after rebuild+update stats?
    You may have to watch the fragmentation too.
  7. preethi Member

Share This Page