High CPU consumed by Resource Monitor due to low virtual memory

We have had a few customers who hit the issue where Resource Monitor consumed a significant portion of CPU in SQL Server 2008.   This is a known issue that has been fixed in SQL Server  2008 RTM CU 6  (http://support.microsoft.com/kb/968722/).   Next CU release of SQL Server 2008 SP1 will also include a fix.

We have only got reports on 32 bit Servers.   The root cause is that something like extended procedure, or COM (loaded by sp_OACreate) exhausted virtual address space of SQL SErver.  SQL Server then is under a low virtual memory state.  This cause Resource Monitor to do extra work.  The best way to address this is to address the virtual memory issue such as using 64 bit or troubleshooting the component that contributed to the memory usage.    To alleviate the CPU overhead, this fix will stop Resource Monitor from doing wasteful work if it detects the memory condition will not go away regardless how hard it works.

 There are a couple of ways you can identify the problem.
First if you take a profiler trace and add the CPU consumed by queries, they don't add up to the total CPU consumed by SQL Server.
Secondly, you can also use perfmon as documented in KB http://support.microsoft.com/kb/968722/ to identify the problem
Finally, you can also use this script to calculate delta CPU consumed by Resource Monitor.  The values should have been very small or close to 0.  If approach .3 or above, you are likely to have hit htis issue.

declare @curCPU int, @prevCPU int, @delta int, @msg varchar(max)
set @curCPU =0
set @prevCPU = 0

while 1 = 1

 select @curCPU = SUM(cpu_time)  from sys.dm_exec_requests where command like '%Resource%Monitor%'
 set @delta = @curCPU - @prevCPU
 set @prevCPU = @curCPU
 set @msg = CAST(GETDATE() as varchar(20)) +  ' -- delta in CPU in sec (wait time 60 sec, ignore first run): ' + CAST((@delta / 1000.00) as varchar(max))
 raiserror (@msg, 10, 1) with nowait
 waitfor delay '0:1:0'

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

Comments (4)

  1. Jeroen Schoenmakers says:

    I'm experiencing this same issue on Microsoft SQL Server 2012 (SP1) – 11.0.3381.0 (X64).

  2. Adam says:

    I'm also experiencing this same issue on Microsoft SQL Server 2012 (SP1) – 11.0.3381.0 (X64).

  3. Matthias Elflein says:

    Same issue on SQL Server 2014: I agree a likely problem may be insufficient memory
    8GB RAM on the board, instance used 6 GB of memory with a fixed size.
    CPU usage was incredible high all the time.
    Finally we found out, there was a second instance running on this machine (test instance, absolutly working nothing). We shut down the second instance to release memory – an cpu usge droped down to 4%.

  4. Jan Krivanek says:

    Same issue here on MS SQL Server 2016 SP1.
    Yes the hosting system is running low on physical memory (but with generous paging file on SSD used only from half).
    We currently cannot offload thi sql server to dedicated machine only for itself, and the high cpu utilization is only delaying the other process that is holding the memory.

Skip to main content