Post reply

What is using resource if all sessions in runnable, suspended, or sleeping status?

  • November 8, 2014 at 11:50 am

    #397621

    I ran sp_who and found no sessions in running state, only runnable, suspended or sleeping? My question is how its possible? How to find what is holding sessions in runnable state?

    Thank you,

    Marina

  • November 8, 2014 at 10:30 pm

    #1757328

    Runnable = waiting for a slot on the CPU. Suspended = waiting for a resource. Sleeping = have nothing to do.

    High CPU usage outside of SQL?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 9, 2014 at 3:41 am

    #1757333

    I see

    Reporting Service 16 %

    Sqlsrv 59 %

    System Idle 13%

    What I don't get, if no running processes why sqlsrv CPU use is 59/%

    Thanks for your reply.

  • November 9, 2014 at 11:11 am

    #1757341

    What I am missing?

    I thought that status from sys.dm_exec_requests should match to status from sp_who2, and they are not?

    SELECT

    a.scheduler_id ,

    b.session_id,

    c.status,

    start_time,

    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,

    ( (CASE WHEN statement_end_offset = -1

    THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)

    ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,

    cpu_time,

    blocking_session_id,

    wait_type,

    wait_time,

    wait_resource,

    open_transaction_count

    FROM sys.dm_os_schedulers a

    INNER JOIN sys.dm_os_tasks b on a.active_worker_address = b.worker_address

    INNER JOIN sys.dm_exec_requests c on b.task_address = c.task_address

    CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS s2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply