SQL Server Performance

CPU usage very low during concurrent large updates

Discussion in 'Performance Tuning for DBAs' started by ggeller, Mar 16, 2010.

  1. ggeller New Member

    Hello,
    We have two updates running concurrently one of which is running an update of a couple million rows. It usually takes a few minutes, but took about a half hour. I was looking at the CPU activity and using sp_who2 throughout the process. The CPU was hovering around 3-7% for awhile. It seems to be hovering around 40-50% for the next update which again is taking longer than normal. When using sp_who2 the two updates seem to spend the majority of their time "sleeping" instead of "runnable". It seems like SQL Server isn't using as many resources as it could, mainly CPU, also it seems one of the updates should be "runnable" in sp_who2. Any opinions would be appreciated. Let me know if any more info is needed.
    Thanks,
    Gunner
  2. Luis Martin Moderator

    Did you update full statistics as regular maintenance?
  3. ggeller New Member

    No, but I did run DBREINDEX to improve the scan density, which was pretty bad. Probably less than 40% on all five indexes.
  4. Luis Martin Moderator

    And what is going on after DBREINDEX?
  5. ggeller New Member

    Same performance issues. It looks like my problems are related to changing a decimal(9,2) to decimal(14,2). I changed them back to (9,2) and I'm getting closer to normal performance. I see a precision of 9 is the cutoff for 4 byte storage tier and 14 would be 9 bytes.
  6. Luis Martin Moderator

    Good catch. Thanks for sharing!

Share This Page