locked
How to troubleshoot high cpu and high memory usage happening on the same time? RRS feed

  • Question

  • Hi Experts.

    What would be the proper troubleshooting approach for the below performance issue.
    Server is 100% cpu and 90% memory usage and users are unable to login.
    What all things need to be checked? IF users cannot login to the server, how can we troubleshoot?

    This was one of the interview question which I have recently attended. I have less knowledge on perf and so looking for some guidance to troubleshoot such issues.

    Thanks in advance.

    Wednesday, July 23, 2014 7:44 PM

Answers

  • Hello,



    I have seen that behavior when indexes need to be created: high memory usage, high cpu usage and high disk IO. Make sure you are defragmenting indexes and updating statistics regularly too.

    http://gallery.technet.microsoft.com/scriptcenter/abf03365-3605-407f-a0ac-8e4e219947bf

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

     


    Wednesday, July 23, 2014 9:04 PM
  • I would not bother much about RAM usage( unless you show me out of memory error) SQL Server is designed as RAM hungry so it is bound to take it set an appropriate value for max server memory and leave enough for OS to function smoothly But of course high CPU is a issue and when High CPU happens look for parallel query and wait types I have seen incorrect parallelism setting causing high CPU usage as well.

    All these are our real world experience what you could face/are facing might have different reason all together you should use suggestions mentioned here to filter down the cause.

    I often refer to below whitepaper

    http://technet.microsoft.com/en-us/library/dd672789%28v=sql.100%29.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


    • Edited by Shanky_621MVP Wednesday, July 23, 2014 10:44 PM
    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:13 AM
    Wednesday, July 23, 2014 10:44 PM
  • I have used this script a lot of times.

    http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:12 AM
    Saturday, July 26, 2014 5:39 AM
  • If the SQL Server service is actually causing the issue, then following the others suggestions of DAC, query plans, recompiles, etc is the correct method.

    However, in my experience, if the server is too busy to let users log in normally, DAC won't let you login either.

    The reason I asked for the @@VERSION is there were several revisions of SQL Server which picked bad query plans, and caused high CPU.

    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:12 AM
    Tuesday, July 29, 2014 6:21 PM

All replies

  • Hi Samantha,

    You can login to SQL Server with Dedicated Admin Connection and query system DMVs even when SQL Server does not accept regular users' connections. If users are unable to login, I would check workers' starvation (e.g. server does not have available worker threads to pick up user connection). You can look at sys.dm_os_waiting_tasks for the tasks waiting for THREADPOOL wait without session_id assigned. In some cases it could happen due massive blocking; however, such condition does not necessarily introduce high CPU condition. 

    select
    	wt.session_id
    	,wt.wait_type
    	,wt.wait_duration_ms
    	,wt.blocking_session_id
    	,wt.resource_description
    from 
    	sys.dm_os_waiting_tasks wt with (nolock)
    order by 
    	wt.wait_duration_ms desc
    option (recompile)

    You can check sys,dm_exec_requests to see what is running now (cross apply with sys.dm_exec_query_text) to see currently executed queries. You can order by cpu_time to see queries that consume large amount of CPU resources.

    select top 10
    	er.session_id
    	,er.start_time
    	,er.cpu_time
    	,er.status
    	,er.command
    	,er.blocking_session_id
    	,er.wait_time
    	,er.wait_type
    	,er.last_wait_type
    	,er.logical_reads
    	substring(qt.text, (er.statement_start_offset/2)+1,
    		((
    			case er.statement_end_offset
    				when -1 then datalength(qt.text)
    				else er.statement_end_offset
    			end - er.statement_start_offset)/2)+1) as SQL
    
    from 
    	sys.dm_exec_requests er with (nolock)
    		cross apply sys.dm_exec_sql_text(er.sql_handle) qt
    order by cpu_time desc
    option (recompile)

    And, of course, high CPU in general needs to be troubleshooted (expensive queries from sys.dm_exec_query_stats; high cpu_time statements capture with XEvents/SQL Traces; CLR usage, bad T-SQL code, XQuery, cursors, etc). High CPU is not necessarily come from the single query. It could be multiple smaller queries contributing to the system load. 

    As for high memory - well, that one needs to be more specific. It is normal for SQL Server to consume as much memory as it can gets. You can see what is using memory (DBCC MEMORYSTATUS, memory clerks, etc) but high memory usage does not necessarily shows the problem. Perhaps, interviewer expected to hear about heavy ad-hoc activity - that one adds pressure for CPU during compilations and wastes system memory by caching single-usage query plans (enabling "Optimize for Ad-Hoc workload" helps to mitigate memory usage). 


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com


    Wednesday, July 23, 2014 8:20 PM
  • Please post the results of SELECT @@VERSION.

    High CPU can be caused by high RAM usage.   I would start with the RAM.

    High RAM usage is almost always caused by not setting "Max Server Memory" correctly.  What is your setting?

    http://support.microsoft.com/kb/321363

    Wednesday, July 23, 2014 8:35 PM
  • Please post the results of SELECT @@VERSION.

    High CPU can be caused by high RAM usage.   I would start with the RAM.

    High RAM usage is almost always caused by not setting "Max Server Memory" correctly.  What is your setting?

    http://support.microsoft.com/kb/321363

    Tom, 

    Out of curiosity, can you please provide a couple of real life scenarios when high RAM usage adds load to CPU? Obviously, there are some conditions that contribute to CPU load/RAM usage (As mentioned Ad-Hoc activity) but high RAM adding load to CPU..?  

    Ok, I can think about some extra CPU load in case of consistent but intermittent memory pressure when SQL Server needs to constantly deallocate and allocate the memory. But I would not call it typical scenario nor assume it would add significant CPU load. 

    And also, we need to define what "High RAM usage" means. Total Server Memory to Target Server Memory? Total Server Memory to OS Memory? Committed memory in OS? 


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com



    Wednesday, July 23, 2014 8:50 PM
  • Hello,



    I have seen that behavior when indexes need to be created: high memory usage, high cpu usage and high disk IO. Make sure you are defragmenting indexes and updating statistics regularly too.

    http://gallery.technet.microsoft.com/scriptcenter/abf03365-3605-407f-a0ac-8e4e219947bf

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

     


    Wednesday, July 23, 2014 9:04 PM
  • Please post the results of SELECT @@VERSION.

    High CPU can be caused by high RAM usage.   I would start with the RAM.

    High RAM usage is almost always caused by not setting "Max Server Memory" correctly.  What is your setting?

    Ah, these are good answers when you are in an interview!

    The correct answer is of course the DAC.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 23, 2014 9:31 PM
  • I would not bother much about RAM usage( unless you show me out of memory error) SQL Server is designed as RAM hungry so it is bound to take it set an appropriate value for max server memory and leave enough for OS to function smoothly But of course high CPU is a issue and when High CPU happens look for parallel query and wait types I have seen incorrect parallelism setting causing high CPU usage as well.

    All these are our real world experience what you could face/are facing might have different reason all together you should use suggestions mentioned here to filter down the cause.

    I often refer to below whitepaper

    http://technet.microsoft.com/en-us/library/dd672789%28v=sql.100%29.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


    • Edited by Shanky_621MVP Wednesday, July 23, 2014 10:44 PM
    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:13 AM
    Wednesday, July 23, 2014 10:44 PM
  • Hi Dmitri V. Korotkevitch and All,

    Thanks for the info. I really appreciate it.

    I did mention below.
    using DAC connection as a back door and execute DMV queries to see what is running currently on the server.

    -Query sys.dm_os_wait_stats to know top waits
    -Query sys.dm_os_waiting_tasks
    -Query sys.dm_exec_query_stats to get cpu intensive queries (ORDER BY total_worker_time DESC) and IO intensive queries (ORDER BY total_physical_reads DESC)
    -For cpu, there could be execessive recompiles(adhoc workload),bad driver,too many traces running, any system defragers running, any antivirus or management s/w. I havent mentioned about CLR as I dont know abt it.
    - For memory, I told look at I/O intensive queries as memory and I/O go hand in hand. 

    Recommendations I have provided the below
    ==============================
    -Always have a dedicated sql server box
    -Be on supportable build
    -update your OS, H/w and Storage drivers
    -optmize for adhoc workloads
    -use resource governor to limit CPU and Memory
    -set max server memory setting
    -create supporting indexes to cut down I/O's
    -keep statistics up to date
    -Take performance baselines and knowing system limits is important


    Ironically, the interviewer doesn't like my answers! That is the reason which made post this question on forum.

    Thursday, July 24, 2014 3:54 AM
  • Ironically, the interviewer doesn't like my answers! That is the reason which made post this question on forum.

    Looks like you are better apt for the position than the interviwer!

    Of course, he may have had a specific scenario in mind. There is always with broad general questions like this one. There are tons of reason the server can behave that way.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 24, 2014 8:28 AM
  • When Windows starts page swapping, this takes CPU.

    Since the OP did say "Server is 100% cpu and 90% memory usage ", looking at SQL Server Database Engine may not have anything to do with the actual problem.  You need to look at the entire server and determine if SQL Server Database Engine is actually the problem or a victim of something else on the server.

    Thursday, July 24, 2014 6:16 PM
  • Hi Tom,

    In Task manager, it is evident that sql server is driving the CPU, then what else can the possibilities of SQL driving High CPU?

    Saturday, July 26, 2014 5:27 AM
  • I have used this script a lot of times.

    http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:12 AM
    Saturday, July 26, 2014 5:39 AM
  • If the SQL Server service is actually causing the issue, then following the others suggestions of DAC, query plans, recompiles, etc is the correct method.

    However, in my experience, if the server is too busy to let users log in normally, DAC won't let you login either.

    The reason I asked for the @@VERSION is there were several revisions of SQL Server which picked bad query plans, and caused high CPU.

    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:12 AM
    Tuesday, July 29, 2014 6:21 PM