SQL Server Performance

CPU performance

Discussion in 'Performance Tuning for DBAs' started by agavilow, Mar 1, 2004.

  1. agavilow New Member

    Hi,

    We are running a SQL 2000 Server and Windows 2000 OS.
    My CPU on the box averaging about 34% during a day. But sometimes it jumps to 100% for about 2-3 min.
    At this time everything is slow. When I look at the Task Manager under Processes I see
    All 100% of cpu has been taking by sqlserv.exe.
    How can I found out which sql process is taking all cpu on my box.

    thanks,
    Alex

    Alex
  2. bradmcgehee New Member

    Most likely, the problem is due to a poorly running query. Try to get a Profiler trace during this heavy activity, and see what is going on. Most likely, you will find a slow-running query which needs to be tuned.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. agavilow New Member

    Thanks for replying back.
    So, it is simply no way to get info. about a transaction which is causing cpu jump to 100%.
    I mean running query against system tables or sp_who2,sp_lock.
    Because it is not good idea to run Profiler trace during my cpu is about 100%.

    thanks

    Alex
  4. ykchakri New Member

    Check the CPUTime column in sp_who2. If a particular process is using CPU heavily, then a high value in this column may give you a clue about this process.

    And then, it is still a good idea to run a profiler to see the activity of this process.
  5. satya Moderator

    The best bet would be to capture counters using PERFMON(SYSMON) and for slow running queries use PROFILER as suggested by Brad.

    Ensure both tools to be executed from a workstation rather on server itself.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page