SQL Server Performance

heavy CPU on on 1 of 16 cpu's...

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SQL_Guess, Oct 17, 2006.

  1. SQL_Guess New Member

    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:

    select
    scheduler_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
  2. joechang New Member

    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
  3. SQL_Guess New Member

    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

Share This Page