SQL Server Performance

database server 100% cpu usage

Discussion in 'General DBA Questions' started by danan_xu, Jun 26, 2005.

  1. danan_xu New Member

    Gents:

    I just encountered one problem with our db server, which has been with 100% cpu usage all day for a few days. Before It was busy with 80% cpu usage avarage, but not 100% all the time.

    After I carefully checked this server, it shouldn't be caused by virus problem.
    some proc or application t-sql code wasn't written properly but definitely they hadn't been changed before the cpu usage greatly increased and stayed at 100%.

    Later, i found the sqlagent.exe process consumes quite high cpu usage as well, the fact is when sqlserver.exe release cpu time, the sqlagent.exe will take most of the cpu time, average about 15-20% all the time. I checked some other servers, but found sqlagent shouldn't take cpu usage all the time.

    Pls give me some advice if you could, I have really no idea what can cause sqlagent.exe consumes high cpu time all the time, appreciate your help.

    thank you very much!
  2. danan_xu New Member

    Just some more extra information:

    When I noticed the 100% cpu usage, I restarted the sqlagent service and the cpu used by sql agent will be down to 1% or 0%, but it will increase slowly and take all cpu resource besides sqlserver.exe.

    thanks,
  3. ghemant Moderator

    Hi,
    have you patched your sql server with SP3 and lattest fixs, another issue is if your t-sql statement / batch contain highly calculated values , too much data and without proper locking hint / re - organize index, would cause this issue.or any transaction that takes more time to review dbcc opentran

    please check if you patched your server with latest fixz and sql server sp3a, and run the following

    dbcc perfmon
    dbcc performance
    pssdiag utility
    DBCC DBREINDEX

    Please read following thread
    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1955&SearchTerms=cpu,usage,100%

    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1962&SearchTerms=cpu,usage,100%

    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1153&SearchTerms=cpu,usage,100%

    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=242&SearchTerms=cpu,usage,100%




    Regards.

    hsGoswami
    ghemant@gmail.com
  4. satya Moderator

    In addition to what said above, as you say when you restart SQLAgent the CPU resources are back to their levels, then I suggest to look under network side for any issues as one of jobs might have problem while executing process.

    The latest service pack on SQL 2000 is SP4, so test it before deploying on production server.

    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.
  5. danan_xu New Member


    thanks for your information.

    sp3 was appatched three years ago, sp4 is on hold.

    this morning I got alerts again ( cpu 100%), after I restart sql agent, it'sgone.

    Normally the cpu usage will be kept 90% for one day until the sql agent cpu usage goes up again.



    quote:Originally posted by ghemant

    Hi,
    have you patched your sql server with SP3 and lattest fixs, another issue is if your t-sql statement / batch contain highly calculated values , too much data and without proper locking hint / re - organize index, would cause this issue.or any transaction that takes more time to review dbcc opentran

    please check if you patched your server with latest fixz and sql server sp3a, and run the following

    dbcc perfmon
    dbcc performance
    pssdiag utility
    DBCC DBREINDEX

    Please read following thread
    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1955&SearchTerms=cpu,usage,100%

    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1962&SearchTerms=cpu,usage,100%

    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1153&SearchTerms=cpu,usage,100%

    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=242&SearchTerms=cpu,usage,100%




    Regards.

    hsGoswami
    ghemant@gmail.com
  6. ghemant Moderator

    you can set the profiler to trace the statements / batch that occupy more time to write for optimizion of it , their must be some batch runnig that consumps very heavy calculation and hence it takes that much of time , in past one of my reports having same issue and we optimize the query batch to achive performance ratio of cpu utilization. or run and check DBCC OPENTRAN -- to review transaction which takes more time to execute / lazy writer.

    Regards.

    hsGoswami
    ghemant@gmail.com
  7. satya Moderator

    Check the processes running during the SQLAGent peakup time.

    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.
  8. danan_xu New Member

    satya:

    how to check the processes? via sql trace? via profiler? via sp_who2?

    throught the windows task manager, can't see any thing else use cpu except sql server, sql server agent.

    What I am wondering is why the sql agent consumes more and more cpu? Anywhere can I check what is using it?
  9. ramesh_vasam New Member

    Thanks for u r information but i am unable to resolve my Issue
    Could you please help me out on this
  10. moh_hassan20 New Member

    [quote user="ramesh_vasam"]
    Thanks for u r information but i am unable to resolve my Issue
    Could you please help me out on this
    [/quote]
    i found the first post by danan_xu in 27 june, 2005 , i.e since more than three years.
    is it not resolved all that time ?
  11. nikmanz76 New Member

    Exclude this location from virus scanning ( :program FilesMicrosoft SQL ServerMSSQLData). SQL Server data files that have the .mdf extension, the .ldf extension, and the .ndf extension.
    http://support.microsoft.com/kb/309422

Share This Page