High CPU Usage by Resource Monitor in SQL Server 2012

Question

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.

Answer 1

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.

Answer 2

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.