locked
PROCESS SQL SERVER TAKING 90%+ CPU USAGE RRS feed

  • Question

  • Hello,

    I am using SQL Server 2008 R2 with one WPF Application on Dell Server R720(2.4GHz) with 8GB RAM. May be 30-40 Clients are connected with this Server. Everything was working perfectly but since last 3-4 days i am facing performance issue. I Check that Sql server is taking almost 90% of CPU usage with about 80 Threads in Resource Monitor. Can anyone guide me what's going on in my Server...

    Attached photos are just to give you idea about server performance. 

    Please help me to find out what's problem with the server

    Thanks


    Jazaib Hussain

    Thursday, June 26, 2014 8:54 PM

Answers

  • Hi,

    Was there any significant change done to your environment in couple of days. was there any hardware change any code change ? Please look first on this aspect this could have caused CPU utilization. Was MAX degree of parallelism changed recently ?

    You can refer to below Microsoft link to troubleshoot high CPU usage. Common cause of High CPU is Parallel queries

    http://blogs.msdn.com/b/batala/archive/2011/07/23/troubleshoot-high-cpu-issue-without-using-profile-traces.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Thursday, June 26, 2014 9:51 PM
  • What is the total number of connections ? is this standalone or cluster instance ? Any recent changes at OS or SQL Instance and DB level ? DB mirroring or Replication is configured ? When was the last SQL Instance restart or OS reboot happened ?

    If the SQL instance is not patch to latest level then please patch it..

    Any maintanance jobs has failed recently ? Index rebuil / Reorg, Update stats, DBCC CheckDB, and any bulk insert and unexpected DB growth ?

    Have you checked the error log for any critical errors ?

    Have you checked what is running on the instance ?

    Try downloading this proceude and delploy it in DBA maintanance DB : sp_whoisactive,  from this link : http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

    This will gives you the complete details what is running and what is the resrouce detials, memory CPU etc..

    Check is there any long running transactions and top 5 wait stats from sys.dm_os_wait_stats ?



    Raju Rasagounder Sr MSSQL DBA



    • Edited by RAJU RG Thursday, June 26, 2014 11:29 PM
    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Thursday, June 26, 2014 11:23 PM
  • hey Jazaib,

    Examine a number of issues:

    1. Name of the process that takes memory
    2. Did it regularly or it changes (if that changes should be checked JOB).
    3. Whether the server software has been updated in recent days that left (you can see updates history).
    4. Whether one server lamps lit (warning light) when one of the hard disks damaged, it builds the RAID array again and it is time consuming processes.
    5. Was great growth or significant change in DB (data compression, adding information, using encryption and opening per cell)?
    6. Attached you a link to check memory use and contains information that can really help you.
    7. Examine the the JOB JOB, and also used in Activity Monitor.
    8. Try to check which users load on the server and create a resource-governor


    Please Mark This As Answer if it helps to solve the issue

    Tzuri Ben Ezra | My Certifications: CompTIA A+ ,Microsoft MCP, MCTS, MCSA, MCITP |
    FaceBook: Tzuri FaceBook | vCard: Tzuri vCard | 
    Microsoft ID: Microsoft Transcript 
     |
     

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Friday, June 27, 2014 3:27 PM
  • Hi Jazaib,

    As other said, you need to find out what was changed. In additional to environment/load changes, extra load could be triggered by suboptimal execution plans due to parameter sniffing and/or stale statistics. For example, you can have the situation when frequently executed query was recompiled using atypical parameter set and cached plan leads to much heavier I/O CPU activity.

    Other factor that often lead to CPU load is bad T-SQL code (multistatement UDF, imperative code, cursors) so check your application.

    You can also run the script below, which returns you the information on most CPU intensive queries in scope of the cached plans. Alternatively, you can setup XEvent/SQL Trace sessions capturing statements with cpu_time exceeding some duration.

    SELECT TOP 50 
    	SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    		((
    			CASE qs.statement_end_offset
    				WHEN -1 THEN DATALENGTH(qt.TEXT)
    				ELSE qs.statement_end_offset
    			END - qs.statement_start_offset)/2)+1) as SQL,
    	qs.execution_count,
    	(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count as [Avg IO],
    	qp.query_plan,
    	qs.total_logical_reads, qs.last_logical_reads,
    	qs.total_logical_writes, qs.last_logical_writes,
    	qs.total_worker_time / qs.execution_count as [Avg CPU],
    	qs.total_worker_time,
    	qs.last_worker_time,
    	qs.total_elapsed_time/1000 total_elapsed_time_in_ms,
    	qs.last_elapsed_time/1000 last_elapsed_time_in_ms,
    	qs.last_execution_time
    from
    	sys.dm_exec_query_stats qs with (nolock)
    		cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
    		outer apply sys.dm_exec_query_plan(qs.plan_handle) qp
    order by -- or vy qs.total_worker_time desc
    	[Avg CPU] desc
    option (recompile)  



    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Friday, June 27, 2014 4:28 PM
  • ---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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Sunday, June 29, 2014 6:54 AM
    Answerer
  • Hi Jazib,

    you've got much hints but best starting point would be the information about the wait stats. Can you please execute the script by Paul Randal which you can find here:

    https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

    and post the results? I think this is a good starting point for further investigation.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Sunday, June 29, 2014 8:36 AM

All replies

  • Hi,

    Was there any significant change done to your environment in couple of days. was there any hardware change any code change ? Please look first on this aspect this could have caused CPU utilization. Was MAX degree of parallelism changed recently ?

    You can refer to below Microsoft link to troubleshoot high CPU usage. Common cause of High CPU is Parallel queries

    http://blogs.msdn.com/b/batala/archive/2011/07/23/troubleshoot-high-cpu-issue-without-using-profile-traces.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Thursday, June 26, 2014 9:51 PM
  • What is the total number of connections ? is this standalone or cluster instance ? Any recent changes at OS or SQL Instance and DB level ? DB mirroring or Replication is configured ? When was the last SQL Instance restart or OS reboot happened ?

    If the SQL instance is not patch to latest level then please patch it..

    Any maintanance jobs has failed recently ? Index rebuil / Reorg, Update stats, DBCC CheckDB, and any bulk insert and unexpected DB growth ?

    Have you checked the error log for any critical errors ?

    Have you checked what is running on the instance ?

    Try downloading this proceude and delploy it in DBA maintanance DB : sp_whoisactive,  from this link : http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

    This will gives you the complete details what is running and what is the resrouce detials, memory CPU etc..

    Check is there any long running transactions and top 5 wait stats from sys.dm_os_wait_stats ?



    Raju Rasagounder Sr MSSQL DBA



    • Edited by RAJU RG Thursday, June 26, 2014 11:29 PM
    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Thursday, June 26, 2014 11:23 PM
  • Hi Jazaib,

    As other said, you need to find out what was changed. In additional to environment/load changes, extra load could be triggered by suboptimal execution plans due to parameter sniffing and/or stale statistics. For example, you can have the situation when frequently executed query was recompiled using atypical parameter set and cached plan leads to much heavier I/O CPU activity.

    Other factor that often lead to CPU load is bad T-SQL code (multistatement UDF, imperative code, cursors) so check your application.

    You can also run the script below, which returns you the information on most CPU intensive queries in scope of the cached plans. Alternatively, you can setup XEvent/SQL Trace sessions capturing statements with cpu_time exceeding some duration.

    SELECT TOP 50 
    	SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    		((
    			CASE qs.statement_end_offset
    				WHEN -1 THEN DATALENGTH(qt.TEXT)
    				ELSE qs.statement_end_offset
    			END - qs.statement_start_offset)/2)+1) as SQL,
    	qs.execution_count,
    	(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count as [Avg IO],
    	qp.query_plan,
    	qs.total_logical_reads, qs.last_logical_reads,
    	qs.total_logical_writes, qs.last_logical_writes,
    	qs.total_worker_time / qs.execution_count as [Avg CPU],
    	qs.total_worker_time,
    	qs.last_worker_time,
    	qs.total_elapsed_time/1000 total_elapsed_time_in_ms,
    	qs.last_elapsed_time/1000 last_elapsed_time_in_ms,
    	qs.last_execution_time
    from
    	sys.dm_exec_query_stats qs with (nolock)
    		cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
    		outer apply sys.dm_exec_query_plan(qs.plan_handle) qp
    order by -- or vy qs.total_worker_time desc
    	[Avg CPU] desc
    option (recompile)  



    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Friday, June 27, 2014 4:28 PM
  • ---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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Sunday, June 29, 2014 6:54 AM
    Answerer
  • Hi Jazib,

    you've got much hints but best starting point would be the information about the wait stats. Can you please execute the script by Paul Randal which you can find here:

    https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

    and post the results? I think this is a good starting point for further investigation.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    • Marked as answer by tracycai Monday, July 7, 2014 11:17 AM
    Sunday, June 29, 2014 8:36 AM