none
CPU utilization of SQL server 2008r2 is high.

    Question

  • Hi All,

    I'm facing a critical issue in one of production servers.sqlserv.exc is utilizing more CPU(90% to 100%)

    Windows version:windows server 2007 standard with SP1
    processor:Intel(R) Xeon(R) CPU X5675 @3.07GHz 2.93GHz
    RAM:4.00GB
    System Type:32 bit operating system.

    SQL server version & edition:SQL server 2008R2 standard edition 32 bit with sp1.

    Please let me know what are the step i need to take to resolve the issue and i would like to know which process is eating more resources.

    Regards,
    Mahi

    Maheshwar Reddy

    Wednesday, January 23, 2013 1:03 PM

Answers

  • Hi,

    Here is some information and links to help you troubleshoot CPU utilization etc.

    http://www.sql-server-performance.com/2008/system-monitor-hardware-bottlenecks/2/

    http://sqlserverdownanddirty.blogspot.com/2011/02/sql-server-schedulers.html

    http://blogs.msdn.com/b/dbrowne/archive/2013/01/18/my-favorite-query-for-investigating-sql-server-performance.aspx

    -- Get CPU utilization by database (adapted from Robert Pearl)  (Query 18)
    WITH DB_CPU_Stats
    AS
    (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
           DatabaseName, [CPU_Time_Ms],
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
    FROM DB_CPU_Stats
    WHERE DatabaseID > 4 -- system databases
    AND DatabaseID <> 32767 -- ResourceDB
    ORDER BY row_num OPTION (RECOMPILE);

    -- Helps determine which database is using the most CPU resources on the instance


    -- Get I/O utilization by database (Query 19)
    WITH Aggregate_IO_Statistics
    AS
    (SELECT DB_NAME(database_id) AS [Database Name],
    CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
    GROUP BY database_id)
    SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
           CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
    FROM Aggregate_IO_Statistics
    ORDER BY [I/O Rank] OPTION (RECOMPILE);

    -- Helps determine which database is using the most I/O resources on the instance

    -- Get total buffer usage by database for current instance  (Query 20)
    -- This make take some time to run on a busy instance
    SELECT DB_NAME(database_id) AS [Database Name],
    CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]
    FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
    WHERE database_id > 4 -- system databases
    AND database_id <> 32767 -- ResourceDB
    GROUP BY DB_NAME(database_id)
    ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

    If you are using a virtual machine this article may interest you:

    http://www.davidklee.net/2012/12/17/cpu-overcommitment-and-its-impact-on-sql-server-performance-on-vmware/

    Good Luck

    Frank


    Frank Garcia

    Wednesday, January 23, 2013 1:37 PM
  • Follow the steps in Troubleshooting SQL Server high CPU usage  you will crack it.


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Proposed as answer by Frank J Garcia Wednesday, January 23, 2013 2:24 PM
    • Marked as answer by Maheshwar Reddy Wednesday, January 23, 2013 3:16 PM
    Wednesday, January 23, 2013 2:18 PM
    Moderator

All replies

  • ---This first thing to check if CPU is at 100% is to look for parallel queries:

    -- Tasks running in parallel (filtering out MARS requests below):
    select * from sys.dm_os_tasks as t
     where t.session_id in (
       select t1.session_id
        from sys.dm_os_tasks as t1
       group by t1.session_id
      having count(*) > 1
      and min(t1.request_id) = max(t1.request_id));

    -- Requests running in parallel:
     select *
       from sys.dm_exec_requests as r
       join (
               select t1.session_id, min(t1.request_id)
              from sys.dm_os_tasks as t1
             group by t1.session_id
            having count(*) > 1
               and min(t1.request_id) = max(t1.request_id)
          ) as t(session_id, request_id)
         on r.session_id = t.session_id
        and r.request_id = t.request_id;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, January 23, 2013 1:16 PM
    Answerer
  • - Please review your SQL Server Error logs & Windows Logs.

    - Few reasons could be due to high number of compilation or recompilation, bad query, poor cursor.

    - Please see SQL Statistics: Batch Requests/sec, SQL Statistics: SQL Compilations/sec, SQL Statistics: SQL Recompilations/sec

    - Try to find top query contributing towards CPU:

    select top 20
        sum(qs.total_worker_time) as total_cpu_time, 
        sum(qs.execution_count) as total_execution_count,
        count(*) as  number_of_statements, 
        qs.plan_handle 
    from 
        sys.dm_exec_query_stats qs
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Wednesday, January 23, 2013 1:25 PM
  • Hi,

    Here is some information and links to help you troubleshoot CPU utilization etc.

    http://www.sql-server-performance.com/2008/system-monitor-hardware-bottlenecks/2/

    http://sqlserverdownanddirty.blogspot.com/2011/02/sql-server-schedulers.html

    http://blogs.msdn.com/b/dbrowne/archive/2013/01/18/my-favorite-query-for-investigating-sql-server-performance.aspx

    -- Get CPU utilization by database (adapted from Robert Pearl)  (Query 18)
    WITH DB_CPU_Stats
    AS
    (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
           DatabaseName, [CPU_Time_Ms],
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
    FROM DB_CPU_Stats
    WHERE DatabaseID > 4 -- system databases
    AND DatabaseID <> 32767 -- ResourceDB
    ORDER BY row_num OPTION (RECOMPILE);

    -- Helps determine which database is using the most CPU resources on the instance


    -- Get I/O utilization by database (Query 19)
    WITH Aggregate_IO_Statistics
    AS
    (SELECT DB_NAME(database_id) AS [Database Name],
    CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
    GROUP BY database_id)
    SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
           CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
    FROM Aggregate_IO_Statistics
    ORDER BY [I/O Rank] OPTION (RECOMPILE);

    -- Helps determine which database is using the most I/O resources on the instance

    -- Get total buffer usage by database for current instance  (Query 20)
    -- This make take some time to run on a busy instance
    SELECT DB_NAME(database_id) AS [Database Name],
    CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]
    FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
    WHERE database_id > 4 -- system databases
    AND database_id <> 32767 -- ResourceDB
    GROUP BY DB_NAME(database_id)
    ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

    If you are using a virtual machine this article may interest you:

    http://www.davidklee.net/2012/12/17/cpu-overcommitment-and-its-impact-on-sql-server-performance-on-vmware/

    Good Luck

    Frank


    Frank Garcia

    Wednesday, January 23, 2013 1:37 PM
  • There is no Tasks running in parallel.what the next step i need to do

    Maheshwar Reddy

    Wednesday, January 23, 2013 1:45 PM
  • SQL Server is rarely CPU dependent.  This is ussually a symptom of page swapping.  What is your "max memory" setting?

    Wednesday, January 23, 2013 1:48 PM
    Moderator
  • min server memory is 0 & max is 1024 mb

    Maheshwar Reddy

    Wednesday, January 23, 2013 2:04 PM
  • Hi Anuragsh,

    I have executed the above query.

    total_cpu_time       total_execution_count number_of_statements plan_handle
    -------------------- --------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------
    1499688869377        43007059              21                   0x05000900F578A275B800943D000000000000000000000000
    1496688457299        4302694               1                    0x05000900B56A9938B8A0471A000000000000000000000000
    1774542102           4300679               1                    0x0500FF7F58996007B8C0AF19000000000000000000000000
    707879881            4032                  2                    0x0500090049C6306AB8A04019000000000000000000000000
    272266593            27277808              4                    0x05000900D21F5E3DB820461A000000000000000000000000
    210065402            17939035              4                    0x0500090060D7753BB8E0431A000000000000000000000000
    182751969            4300679               1                    0x0600090004DE362AB8C07519000000000000000000000000
    33833965             630247                10                   0x05000800316F5610B8A0751A000000000000000000000000
    29180665             337035                3                    0x05000800122E677AB8A0751B000000000000000000000000
    26420892             389566                2                    0x060005007D09C820B8E0591A000000000000000000000000
    17394528             771472                1                    0x0500050095555D02B8804D1A000000000000000000000000
    16522459             389614                2                    0x06000500149D0C1BB8A0741A000000000000000000000000
    15417970             231270                4                    0x050008008A1ACF2FB8A0821B000000000000000000000000
    14703123             653958                9                    0x05000800AEB9A870B8E0351B000000000000000000000000
    12751961             675033                5                    0x0500080036CD403BB8A0531B000000000000000000000000
    11186522             2663                  4                    0x05000D007ABA7A26B840E43C000000000000000000000000
    9137697              24567                 1                    0x05000D005D05B621B8E01136000000000000000000000000
    7339844              469120                7                    0x0500080042154022B8607E1B000000000000000000000000
    6237295              349288                12                   0x05000800FDA84C3AB840631B000000000000000000000000
    5083983              6462                  2                    0x050005000D42C537B8604643000000000000000000000000

    (20 row(s) affected)

    from that how can i find the culprit which is taking more CPU


    Maheshwar Reddy

    Wednesday, January 23, 2013 2:07 PM
  • HI Frank,

    I have executed the below query.I found intresting resuls.

    WITH DB_CPU_Stats
    AS
    (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
           DatabaseName, [CPU_Time_Ms],
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
    FROM DB_CPU_Stats
    WHERE DatabaseID > 4 -- system databases
    AND DatabaseID <> 32767 -- ResourceDB
    ORDER BY row_num OPTION (RECOMPILE);

    result:

    row_num              DatabaseName                                                                                                                     CPU_Time_Ms          CPUPercent
    -------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------------------------------
    1                    JCIHistorianDB                                                                                                                   2998423973116        99.99
    2                    XMS                                                                                                                              146511652            0.00
    3                    ReportServer                                                                                                                     71525351             0.00
    4                    NavTreeCache                                                                                                                     22569330             0.00
    5                    JCIEvents                                                                                                                        11911108             0.00
    6                    JCIAuditTrails                                                                                                                   1452146              0.00
    7                    MetasysIII                                                                                                                       345694               0.00

    (7 row(s) affected)

     i can find that JCIHistorianDB is utilizing 99.99 percentage.then what is next measure to look.


    Maheshwar Reddy

    Wednesday, January 23, 2013 2:14 PM
  • Follow the steps in Troubleshooting SQL Server high CPU usage  you will crack it.


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Proposed as answer by Frank J Garcia Wednesday, January 23, 2013 2:24 PM
    • Marked as answer by Maheshwar Reddy Wednesday, January 23, 2013 3:16 PM
    Wednesday, January 23, 2013 2:18 PM
    Moderator
  • I would follow Karthick's advice and utilize the link he provided. You are close to where the issue is occurring.

    I am happy you are finding the culprit.

    Frank.


    Frank Garcia

    Wednesday, January 23, 2013 2:25 PM
  • Thanks  a lot to all of you.

    I found the culprit.one stored procedure.


    Maheshwar Reddy

    Wednesday, January 23, 2013 3:13 PM
  • That's good to hear Maheshwar. Can you post what you did to isolate that one stored procedure so others that experience the same or similar issue in the future can make use of this thread?

    Thanks!

    Frank.


    Frank Garcia

    Wednesday, January 23, 2013 3:18 PM
  • Hi karthick,

    One small doubt when i have executed the query for finding SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers .I got the below output .But how can we know which process is utilizing more cpu .

    From my below out put could you please explain how can we find out which process is utilizing more cpu

    record_id   EventTime               system_cpu_utilization sql_cpu_utilization
    ----------- ----------------------- ---------------------- -------------------
    70052       2013-01-23 07:23:43.910 87                     82
    70051       2013-01-23 07:22:43.813 89                     84
    70050       2013-01-23 07:21:43.757 89                     83
    70049       2013-01-23 07:20:43.697 84                     77
    70048       2013-01-23 07:19:43.623 88                     82
    70047       2013-01-23 07:18:43.497 88                     81
    70046       2013-01-23 07:17:43.433 88                     84
    70045       2013-01-23 07:16:43.377 87                     82
    70044       2013-01-23 07:15:43.270 88                     84
    70043       2013-01-23 07:14:43.207 88                     84
    70042       2013-01-23 07:13:43.140 89                     83
    70041       2013-01-23 07:12:43.087 88                     84
    70040       2013-01-23 07:11:42.937 89                     83
    70039       2013-01-23 07:10:42.870 88                     83
    70038       2013-01-23 07:09:42.823 86                     80
    70037       2013-01-23 07:08:42.760 87                     80
    70036       2013-01-23 07:07:42.610 86                     81
    70035       2013-01-23 07:06:42.550 89                     83
    70034       2013-01-23 07:05:42.497 86                     80
    70033       2013-01-23 07:04:42.440 86                     81

    (20 row(s) affected)


    Maheshwar Reddy

    Wednesday, January 23, 2013 3:25 PM
  • I have updated the stats of the database where the stored  procedure resides and i found the missing indexes as well

    Maheshwar Reddy

    Wednesday, January 23, 2013 3:37 PM
  • Let me know if this helps you until Karthick replies.

    http://www.g-productions.nl/index.php?name=dm_os_ring_buffers


    Frank Garcia

    Wednesday, January 23, 2013 4:21 PM
  • Hi karthick,

    One small doubt when i have executed the query for finding SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers .I got the below output .But how can we know which process is utilizing more cpu .

    From my below out put could you please explain how can we find out which process is utilizing more cpu

    record_id   EventTime               system_cpu_utilization sql_cpu_utilization
    ----------- ----------------------- ---------------------- -------------------
    70052       2013-01-23 07:23:43.910 87                     82
    70051       2013-01-23 07:22:43.813 89                     84
    70050       2013-01-23 07:21:43.757 89                     83
    70049       2013-01-23 07:20:43.697 84                     77
    70048       2013-01-23 07:19:43.623 88                     82
    70047       2013-01-23 07:18:43.497 88                     81
    70046       2013-01-23 07:17:43.433 88                     84
    70045       2013-01-23 07:16:43.377 87                     82
    70044       2013-01-23 07:15:43.270 88                     84
    70043       2013-01-23 07:14:43.207 88                     84
    70042       2013-01-23 07:13:43.140 89                     83
    70041       2013-01-23 07:12:43.087 88                     84
    70040       2013-01-23 07:11:42.937 89                     83
    70039       2013-01-23 07:10:42.870 88                     83
    70038       2013-01-23 07:09:42.823 86                     80
    70037       2013-01-23 07:08:42.760 87                     80
    70036       2013-01-23 07:07:42.610 86                     81
    70035       2013-01-23 07:06:42.550 89                     83
    70034       2013-01-23 07:05:42.497 86                     80
    70033       2013-01-23 07:04:42.440 86                     81

    (20 row(s) affected)


    Maheshwar Reddy


    SQL Server ring buffer captures only the SQL Server CPU utilization and System CPU utilization. We do not have a way to collect historical CPU utilization of all processes from ring buffer. You would have to use perfmon or Wmi scripts to collect CPU utilization by individual processes.


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Thursday, January 24, 2013 5:59 AM
    Moderator