6

Has anyone encountered this before? I have a two node cluster with SQL Server 2012 SP1, Enterprise enterprise installed on Windows 2012. These are VMs running on VMWare 5.1. I have noticed that occasionally the CPU would spike all the way up to 100% and be sustained for a while. When I checked to see what was utilizing that much CPU it turned out to be the Resource Monitor. I know there was an issue with SQL Server 2008 with high CPU usage when virtual memory is low (KB 968722) but it was fixed in a service pack.

Is anyone seeing the same thing with SQL Server 2012 SP1? It's the exact same situation as mentioned in KB 968722 but instead of SQL Server 2008, it's happening on SQL Server 2012.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2665372
  • 193
  • 1
  • 2
  • 5
  • [High memory, high CPU - have you considered that VMWare might be the problem](http://stackoverflow.com/questions/18132330/seeing-high-memory-usage-in-sql-server-2012)? – Aaron Bertrand Aug 08 '13 at 22:36
  • We have other environments that are running the same thing and I don't see the memory or cpu issues that I am seeing on this box. They are all VMs. – user2665372 Aug 08 '13 at 23:06
  • Ok, let me be more specific: the VMWare configuration on *this* server. How many VMs is the host running? Are they all equivalent in terms of vCPUs and vRAM, or do you have some lopsiding going on? – Aaron Bertrand Aug 08 '13 at 23:07
  • I believe the VM Host is just one for now but I need to check on that. We might just need more CPU and RAM. The other server that doesn't have this issue has 4 CPU & 32gb RAM compared to 2 CPU and 16 gb of RAM. I don't know why I didn't even thing about that. Thanks for the tip, I will try to see if we can get more added and see if that helps the situation. – user2665372 Aug 08 '13 at 23:26
  • I'm definitely seeing the same thing, ridiculous that you should need 32 GB to get reasonable performance from SQL server. – Dylan Nicholson Feb 13 '14 at 22:34
  • Yes, I am seeing the same behaviour on a Windows Server 2012 R2 Server running SQL Server 2012 SP1 11.0.3128.0 (Express 64 bit) and a second instance running SQL Server 2014 12.0.2000.8 (Developer 64-bit). The SQL 2012 instance is running high CPU on the RESOURCE MONITOR command when I run sp_who2. The 2014 instance is fine........NOTE: This is a VM but it is running on a WIndows Server 2012 R2 Hyper V instance. – AnthonyJ Apr 01 '15 at 00:48

2 Answers2

4

I just face a similar issue. Our windows team reported to me (SQL DBA) that we have one server with high CPU on only 2 cores (server has 10 cores). This server is part of a 2 node cluster and has 3 SQL Instances installed. One of those instances was causing the CPU issue, and it was very surprise that the instance causing the issue was the one doing nothing. This instance was installed but was not being used yet and it was causing CPU issues already on only 2 cores. Using Thread Object performance counters I identified the thread id that later I used to query sys.sysprocesses table to find those thread IDs (KPID).

SELECT * FROM sysprocesses
WHERE kpid IN (<Thread IDs>)

With that query I identified the Session IDs on SQL Server, they were background processes. Using sp_who one session ID cmd was: "RESOURCE MONITOR" and the other one was: "LAZY WRITER".

I verified memory, and since this instance was new, it was configured with min server memory as 1024 mb and max server memory as 1024 mb. I increased the max server memory setting to 2048 mb and the problem went away instantly.

I know this is not a universal solution, it was for my environment due to my context but hope it helps if somebody else is reading this question as well.

Victor Barajas
  • 193
  • 1
  • 9
4

Quick answer (if > SQL Server 2008): then RESOURCE MONITOR is probably taking high CPU time because SQL server is lacking sufficient RAM.

  • Check your windows task manager / resource monitor for unnecessary ram-intensive processes. Clean it up.
full_prog_full
  • 1,139
  • 10
  • 17