SQL Server Performance

sqlservr.exe 95% cpu time

Discussion in 'Performance Tuning for DBAs' started by patrick, May 11, 2005.

  1. patrick New Member

    the sqlserve.exe process is eating up all the cpu time all of the sudden on one server. I ran a profiler trace and no queries are jumping out at me as far as duration or cpu time. Does anyone have any suggestions on where to look? Thanks.
  2. Luis Martin Moderator

    Is SQL server dedicated?
    Did you check if there is any scheduled job working?

    Use Peformance Monitor to check CPU, Memory and disk.

    Luis Martin

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    All postings are provided “AS IS” with no warranties for accuracy.

  3. dineshasanka Moderator

  4. ghemant Moderator

    Dear Mr. Pratik,
    Please check wether your SQL Server has patched with Latest Service Pack ? try installing Service Pack 3a for MS SQL Server, and the other issue would be from your application code,the connection opened and close ...?

  5. mmarovic Active Member

    It doesn't have to be just one query execution causing high cpu. High load can be because of many executions per second. When you analyze trace better group by sp name or large enough substring if your queries are not sp executions only.
  6. patrick New Member

    The following update statement runs constantly under the same SPID 56:

    UPDATE STATUS SET Duration = 392, EndTime = 1115902989 WHERE ID = 7432805

    It updates a table for phone logs. It currently has 7432647 records in it, and no primary or foreign keys or indexes.

    The spid has a CXPACKET waittype and is using 133498081 cpu time.

    Could this be the cause? Is there anything I can do to alleviate this? Any other info anyone needs? Thanks.

  7. mmarovic Active Member

    quote:Could this be the cause?
    It could.
    quote:Is there anything I can do to alleviate this?
    Create clustered pk on ID column. Let us know if it helped. Thanks.
  8. Adriaan New Member

    A table with no primary key and no indexes? Not clever at all. Could the update statement be issued by a client app?
  9. jigar_pcs New Member

    What you need is to trace the activity on your sql server with profiler (not using GUI) for some time and then summarize the activity on sql server for CPU. Then you can come up with the information about query or procedure taking maximum CPU on your server and attack that.

    Jigar Patel
  10. ghemant Moderator

    Dear Sir,
    The problem arise here when u have some around 74lacks of records in a table that used to update,insert,delete and select statement and it doesn't have PK or INDEX ... it will definately down your server's performance... would u let us know how often this table used ? more often is use will affect more the performance of your SQLSERVER


Share This Page