SQL Server – v2012 – High CPU // RESOURCE MONITOR

Background

Here I am trying to steal a bit of work on my brother’s computer and I noticed a Symantec – Norton Anti-Virus Alert.

The Alert states that an Application is using quite a bit of CPU.  And, so I am thinking go away, this is just a home PC used for Development Stuff (C#, but lately more Java Script Framework stuff).

Compared to my machines, his is good – 64-Bit, 2 CPUs, 6 GB of Memory.

So if it is my machine, I will ignore.  But, on his computer and knowing no one is doing anything on his machine, let us go see what is going on.

Why Symantec?

Well as I was comparing Anti Virus (AV) Rescue CD Vendors a couple of weeks ago, I found out that I can get Symantec’s Norton for free.  It is available for 6 months and your path is documented here:

So I know you ‘re saying and thinking big deal.  You use GfiSoft/AVG (http://free.avg.com/us-en/homepage, but my machine is a full-blown server (MS Windows 2012).  And, just not sure of how many vendors give us free AV for Server OSes.

Is it a real problem or just false alert from monitoring tool?

So I went ahead and clicked on the prompt and got a fuller message, like the one pasted:

Symantec-FileInsight

So it looks to be real enough.

Which MS SQL Server Processes is using up CPU?

Let us determine which SQL Server Processes are using up CPU…

;with cte
( 
   [totalCPU]
)
as
(
   select sum(cpu)
   from   master.dbo.sysprocesses
)

select 
	  tblSysprocess.spid
	, tblSysprocess.cpu
	, (tblSysprocess.cpu * 100) / cte.totalCPU 
            as [percentileCPU]
	, tblSysprocess.physical_io
	, tblSysprocess.memusage
	, tblSysprocess.cmd
	, tblSysProcess.lastwaittype
from   master.dbo.sysprocesses tblSysprocess
         cross apply cte
order by tblSysprocess.cpu desc
go

Output:

SessionProcessesOrderedByCPU


Explanation:

  • There we have it, by far a process registered with the name “RESOURCE MONITOR” is using up most of our CPU
  • And, it is waiting quite a bit on PREEMPTIVE_XE_CALLBACKEXECUTE
  • Wait types bearing the signature PREEMPTIVE_XX are well chronicled in CSS SQL Server Engineers – The SQL Server Wait Type Repository… ( http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx )
  • The blog post promises more to come but for now it states that “Used to indicate a worker is running coded that is not under the SQLOS Scheduling Systems”

SQL Profiler …

Wanted to see what commands are coming across on what should be a very silent and un-used SQL Server Instance.

So launched SQL Profiler and at this time the only active requests is the “SQL Server “Reporting Services” trying to see if there is work to be done.

Access Windows Service Applet and stopped that service and changed it start mode to “manual”.

That took Reporting Services out of the way.

But, still nothing coming in over the wire via SQL Server Profiler.  I suppose that SQL Server System processes are not tracked.

 

Task Manager (Order by CPU)

Is CPU still busy?

TaskManger with SQL Service On Top

Yes, SQL Server is still on top taking up 20% of used CPU and the CPU itself  is 29% of max.

SQL Server Error Log

I am seeing a few entries stating memory pressure:

errorLog

Memory Problem

Jonathan Kehayias has the most accessible script out there to detect memory pressure, how often they are occurring, and how they are being sourced (Internal to SQL Server or System)

Identifying External Memory Pressure with dm_os_ring_buffers and RING_BUFFER_RESOURCE_MONITOR
http://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/


--System Memory Usage
SELECT 
   EventTime
 , record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') 
      as [Type]
 , record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') 
      as [IndicatorsProcess]
 , record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int')
      as [IndicatorsSystem]
 , record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') 
      AS [Avail Phys Mem, Kb]
 ,  record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint')       AS [Avail VAS, Kb]
FROM (
    SELECT
        DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks )))
           - [timestamp])/1000), GETDATE()) AS EventTime,
        CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    CROSS JOIN sys.dm_os_sys_info
    WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab
ORDER BY EventTime DESC;

Output:

RING_BUFFER_RESOURCE_MONITOR

 

 Explanation:

  • Quite a lot of entries came in on 9/21/2013 @ 8:26 bearing the Type = RESOURCE_MEMPHYSICAL_LOW
  • Some are sourced by the SQL Server Process — IndicatorProcess != 0
  • But, most are sourced by the System — IndicatorSystem != 0

Task Manager (Order by Memory)

Who is using memory?

Task Manager - Highest Users of memory

Explanation:

  • So brother uses Firefox and it is good
  • I much prefer Chrome, but Google opens up a new process for Window\Tab and memory usage can get a bit out of hand; especially if you do not go back and close those windows
  • Let us close some of them

Diagnostic Queries

Here are a couple of other diagnostic queries

Jack Li
http://blogs.msdn.com/b/psssql/archive/2009/08/20/high-cpu-consumed-by-resource-monitor-due-to-low-virtual-memory.aspx


declare @curCPU int
declare @prevCPU int
declare @delta int
declare @msg varchar(max)

declare @Iteration int
declare @iMaxNumberofIterations int

set @curCPU =0
set @prevCPU = 0

set @Iteration = 1
set @iMaxNumberofIterations = 5

while (
	 (1 = 1)
	and (@Iteration <= @iMaxNumberofIterations+1)      
     ) 
begin  

    select @curCPU = SUM(cpu_time)  
    from sys.dm_exec_requests 
    where command like '%Resource%Monitor%' 

    set @delta = @curCPU - @prevCPU  
    set @prevCPU = @curCPU  

    if (@Iteration &gt; 1)
    begin

	 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
    end

    waitfor delay '0:1:0'

    set @Iteration = @Iteration + 1

end

Output:

JackLi

Supporting Web Literature

High CPU Consumed by Resource Monitor Due to Low Virtual Memory

http://blogs.msdn.com/b/psssql/archive/2009/08/20/high-cpu-consumed-by-resource-monitor-due-to-low-virtual-memory.aspx

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.

Advisory

  • Be careful what Applications you have running on a box that has MS SQL Server running
  • Consider setting “Min Server memory” though not sure that the SQL Instance will not do the gentlemanly thing of trying to respond to the OS requests to release memory
  • High CPUs are more often than not reflective of an over-exercised system.  In this case, due to low memory, SQL Server is being asked to help out the OS and see if it can give some memory back and it is dutifully responding

References

References – Setting Min\Max Memory

References – Identifying Memory Pressure

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s