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.
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:
- Free Norton Anti-Virus 2013 Product Key 6 months
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:
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
- 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?
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:
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
--System Memory Usage SELECT EventTime , record.value('(/Record/ResourceMonitor/Notification)', 'varchar(max)') as [Type] , record.value('(/Record/ResourceMonitor/IndicatorsProcess)', 'int') as [IndicatorsProcess] , record.value('(/Record/ResourceMonitor/IndicatorsSystem)', 'int') as [IndicatorsSystem] , record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)', 'bigint') AS [Avail Phys Mem, Kb] , record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)', '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;
- 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?
- 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
Here are a couple of other diagnostic queries
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
Supporting Web Literature
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.
- 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 – Setting Min\Max Memory
- Effects of Min\Max Server Memory
References – Identifying Memory Pressure
- Identifying External Memory Pressure with dm os ring buffers and ring buffer resource monitor
- High CPU Consumed by Resource Monitor due to low virtual memory