SQL Server Performance Forum – Threads Archive
heavy CPU on on 1 of 16 cpu’s…
Hi all. We have an interesting situation we are trying to understand. Running SQL 2005 64 bit SP 1 hotfix rollup (9.0.2153) on SQL 2003 SP 2. We have a ’16 cpu’ (4 x dual core, hyper threaded) box. We are seeing one CPU running at between 25 to 40 % usage consistently, over the course of an entire day. We’ve stopped and started unnecessary services at times when we could (i.e. SQL Server agent when nothing was running) – but no dice – that single CPU continues to run hot, while the other cpu’s run between 5-10%. SQL in not constrained to only use 1 CPU, and is using the others: selectscheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255 gives us between 1 and 4 current tasks on each CPU. I’m trying to find a way to drill down to find the actual SQL
select ‘select * from sys.dm_exec_sql_text (‘+master.dbo.fn_varbintohexstr(sql_handle)+’)’ ,
‘dbcc inputbuffer(‘+convert(varchar,der.session_id)+’)’
,*
from sys.dm_os_tasks (nolock) dot
inner join sys.dm_exec_requests (nolock) der
on dot.request_id = der.request_id
where dot.scheduler_id = 15
and ‘select * from ::fn_get_sql (‘+master.dbo.fn_varbintohexstr(sql_handle)+’)’ is not null
and der.session_id <> @@SPID
order by 1 desc
but I just can’t find wanything that has been running for hours, using that much CPU. any ideas how I can find this – since it appears the spid has no intention of enmding, I’m not sure that we will find this with a trace… Panic, Chaos, Disorder … my work here is done –unknown
use task manager -> performance tab,
go to View to set Show Kernel Time on is it one of the upper CPU’s?
is the kernel time for that CPU high? if so, see my paper on High Call Volume apps
Hi Joe, thanks for that – didn’t think to turn on kernel time when I checked the task manager yesterday 🙁 It *appears* that the issue has been resolved – CPU 15 now running on around 15% or so at the moment, and we are running an index defrag. It does appear that CPU 15 seems to be the ‘cpu of first call’ as it were. Is your paper under the articles section here? Cheers Panic, Chaos, Disorder … my work here is done –unknown
]]>