• 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