locked
SQLSRVR.exe high CPU usage RRS feed

  • Question

  • Hello ,

    We have SQL server 2000 and overall CPU usage is below 30 % but the SQLSRVR.exe CPU usage is above 100 % .

    Is someone help me from where do I start to do investigation ?

    One more thing the SQL server min and max memory is not set up .They are default .So I think this is one of the reason SQL is trying to consume all OS memory .That why I can see SQL server consuming high CPU usage .

    Please correct me if I could be wrong .

    Thank You ,

    Daizy

    Tuesday, December 20, 2011 8:34 PM

Answers

  • U may start yr investigation from 

    1. turn on SQL profile to see the activity of the SQL Server

    2. SQL Server Activity Monitor also can help U see IO, Resource Wait, and active user task. 

    3 Also there are a lot of DMVs for U dig into 

     

    Tony :)

    • Marked as answer by Maggie Luo Wednesday, January 4, 2012 9:39 AM
    Thursday, December 29, 2011 2:58 AM

All replies

  • Daizy

    How powerful is your server? How much RAM does it have? Setting MAX memory  may be helpful

    Do not forget tuning queries , there is also place for improvement


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 21, 2011 6:33 AM
  • Hi Daizy,

    Are there any long running jobs (Backup or Reindexing Jobs)? How long it's been you have done database reindexing or update statistics?

    You could try to install SQL Server 2000 Service Pack 4.
    Download SQL Server 2000 Service Pack 4: http://www.microsoft.com/download/en/details.aspx?id=18290.

    For more information, you could refer to http://support.microsoft.com/kb/930484.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Thursday, December 22, 2011 2:53 AM
  • Total Pysical RAM  SQL server 2000 server is 3GB.

    Here are some counters values

    SQLSERVR process private byte - 1767342080

                                  working set -1772380160

    SQL server : memory manager

             target server memory (KB) 1661936

             total server memory (kb)    1661936

     

    Would you please suggest me how much memory do I set for sql server max ?

    We will upgrade the SQL server from 2000 to 2008 in couple of months .

    For now, I need to improve the SQL server 2000 perfomance .

    The statistics are updated up to date and scheduled job run to reindex every night .

    Thank You,

    Daizy Desai

     

     

     

    Thursday, December 22, 2011 7:02 PM
  • With default memory settings, SQL server will try to utilize full Server memory. We should leave some room for Server operations too. I suggest to leave atleast 20% of available memory to OS.

    Try out adjusting the memory:

    USE master
    EXEC sp_configure 'max server memory (MB)', 2000
    RECONFIGURE WITH OVERRIDE

    also you can
    1. Look out the queries which are being executing causing 100% utilizations
    2. SQL Jobs runing in back ground
    3. Any DB backup utilities.

    Thanks,
    Loknath

     

     


    Loknath Vendra
    Thursday, December 22, 2011 8:16 PM
  • U may start yr investigation from 

    1. turn on SQL profile to see the activity of the SQL Server

    2. SQL Server Activity Monitor also can help U see IO, Resource Wait, and active user task. 

    3 Also there are a lot of DMVs for U dig into 

     

    Tony :)

    • Marked as answer by Maggie Luo Wednesday, January 4, 2012 9:39 AM
    Thursday, December 29, 2011 2:58 AM
  • It is hard to suggest as 3gb is tooooooo little.... Also, I would turn on SQL Server profiler , group by CPU and trying to identify the queries take more cpu...

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 29, 2011 6:21 AM