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 > 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

Microsoft – SQLServer – High CPU – SPID – 1

The last few days has being interesting for this particular MS SQL Server Instance.

One weekend afternoon I saw it so busy.  As a DBA sometimes you learn a bit more from under-provisioned machines.  I hope I can learn a bit from this one.

Here is what I found so far:

  • Take it off the Internet.  It was being attacked and their were numerous entries of failed logins (sa – username no less)
  • Configured Symantec AntiVirus to exclude checking Network and MS SQL Server files; files with “known” SQL Server Extensions (mdf, ndf, ldf)
  • Configured Symantec AV to exclude C:\WINDOWS\system32\NavLogon.dll (http://www.symantec.com/connect/forums/rtvscanexe-high-memory-usage-sav)

Though these changes are in place and server restarted, some simple queries were still timing out and SQL Instance was still registering high CPU even when no queries were being processed.

From experience I know that Full-Text Search sometimes works in the background, but that did not appear to be the case at this time.

So ran a simple query looking for which Session was using high CPU.

SELECT tblSysProcess.cpu as processor, *
from master.dbo.sysprocesses tblSysProcess
order by tblSysProcess.cpu desc

And, here is what we got back:

So after a couple of days running this same query, SPID 1 stayed on top of our list of top CPU users.

Took the easy way and Googled for “SQL Server High CPU SP 1” and ended up where it made sense.

High CPU Usage for SPID #1 (SQL Server 2008)

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/bd9e4476-39e2-46f8-a426-bef9601f9ad9

In this particular post, Leila (posting as Amin S) did all the background work and asked the same question I was going to ask:

Hi all,

I have SQL Server 2008 (SP1) on Windows Server 2003 (R2).

Sometimes this server becomes very slow and task manager indicates high CPU usage. I added all SQL Server related threads in Performance Monitor to monitor Processor Time% per Thread.

By correlating Thread ID and KPID, I realized that this abnormal increase for CPU usage is always related to SPID #1 which is system process thus cannot be killed. After restarting SQL Server, everything works fine but after few minutes, exactly the same situation happens and SPID #1 consumes almost whole capacity of processors. I tried different ways to determine the source of problem. First I took our main database (financial) to offline state, and turned it back online. All users for this database were killed and SPID #1 released the processor! Sometimes this solution does not work and I use a loop to kill all users in every database. This works in most of times, but today I encountered situation that even killing all users did not work and I forced to restart the SQL Server engine several times during the day.

I was wondering if somebody could suggest a test for further observation or any solution for this issue.

Many thanks in advance,

Leila

Like I said, Leila did all the work.  A day later (s)he came back and answered his\her own question.

Leilia found a MS Link:

FIX: The CPU usage of the resource monitor is very high when the virtual memory is running low in SQL Server 2008

http://support.microsoft.com/kb/968722/en-us

And so it seems the next thing to do is determine the OS Thread ID for the “Resource Monitor”. This query will do so:

SELECT 
		  STasks.session_id
		, SThreads.os_thread_id
		, b.command 
FROM sys.dm_os_tasks AS STasks
	INNER JOIN sys.dm_os_threads AS SThreads 
		ON STasks.worker_address = SThreads.worker_address
	LEFT OUTER JOIN sys.dm_exec_requests b 
		ON STasks.session_id = b.session_id
WHERE STasks.session_id IS NOT NULL 
and command = 'RESOURCE MONITOR' 
ORDER BY sthreads.os_thread_id

I tried using “Performance Monitor” \ Perfmon. But, really not able to filter by Thread ID.
Probably should have tried SysInternals \ ProcessMonitor; as it has ability to observe at Thread ID, as well.

Need to address Memory Issue.

But, I think I have enough comfort level, that nothing will be hurt by upgrading from MS SQL Server v2008 SP1 to SP2.

Off we go.

 

References: