First published on MSDN on Aug 20, 2009
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
begin
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'
end
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support
