none
High CPU usage caused by SQL Server

    Question

  • I have the following hardware:

    AMD Opteron 252 @ 2.61Ghz
    1GB RAM
    SCSI HD's

    This is a dedicated machine for TFS. It runs only SQL Server 2005 Standard Edition with SP2, WSS2 and TFS with TFS SP1 installed. I am the only one using it since it hasn't gone live yet. I am experiencing high CPU usage by the SQL Server process, peaks to 90% with an average of 40%, but I am not able to pinpoint the problem.

    I have a total of 3 Team Projects which I have created for testing purposes. Because of the high CPU usage I sometimes get presumably timeouts when i open the "Work Items" folder. also when I try to create a new Team Project I have to wait for about a minute before I can choose from the installed Process Templates.

    I do believe that this started with the installation of Service Pack 2 for SQL Server 2005. Can someone help me troubleshooting this? Or figure out what is causing this high CPU usage and how to solve this. This TFS will be used by about 30 and I have to explain my manager why I am already experiencing these kind performance issues with only one user.

    Monday, March 26, 2007 9:01 AM

Answers

  • 1. You should start by checking what processes are currently executing on the server. If you run the statement below in SQL Management studio, it will display a list of user processes currently executing:

    SELECT *

    FROM sys.dm_exec_requests a

    OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b

    WHERE session_id > 50

    and session_id <> @@spid

    2. If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output)

    RPC: Completed (Under stored procedures)

    SQL: BatchCompleted (Under TSQL)

    Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value.

    Please do send back results once you have any.

     

     

    Monday, March 26, 2007 1:18 PM

All replies

  • The place to start is by attaching the SQLProfiler to the SQL box and see what SQL thinks it is doing.  I'll get someone with good SQL experience to follow up on this thread.

    Brian

    Monday, March 26, 2007 12:39 PM
  • 1. You should start by checking what processes are currently executing on the server. If you run the statement below in SQL Management studio, it will display a list of user processes currently executing:

    SELECT *

    FROM sys.dm_exec_requests a

    OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b

    WHERE session_id > 50

    and session_id <> @@spid

    2. If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output)

    RPC: Completed (Under stored procedures)

    SQL: BatchCompleted (Under TSQL)

    Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value.

    Please do send back results once you have any.

     

     

    Monday, March 26, 2007 1:18 PM
  • I have been to the Developer & IT Pro Days 2007 in Belgium the last couple of days. I will try this first thing tomorrow morning and keep you guys posted.
    Thursday, March 29, 2007 6:51 PM
  • I was having the same issue, Profiler shows the GetWarehouseData stored procedure firing every second, as well as a few other queries.
    Friday, August 13, 2010 6:42 AM
  • Thank you for this useful information ....:)
    Saturday, August 11, 2012 7:17 AM