WITH sess AS

(

    SELECT

        es.session_id,

        database_name = DB_NAME(er.database_id),

        er.cpu_time,

        er.reads,

        er.writes,

        er.logical_reads,

        login_name,

        er.status,

        blocking_session_id,

        wait_type,

        wait_resource,

        wait_time,

        individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),

        parent_query = qt.text,

        program_name,

        host_name,

        nt_domain,

        start_time,

        DATEDIFF(MS,er.start_time,GETDATE()) as duration,

        (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan

    FROM

        sys.dm_exec_requests er

        INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id

        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

    WHERE

        es.session_id > 50

        AND es.session_Id NOT IN (@@SPID)

)

SELECT

    *

FROM

    sess

UNION ALL SELECT

    es.session_id,

    database_name = '',

    0,

    0,

    0,

    0,

    login_name,

    es.status,

    0,

    '',

    '',

    '',

    qt.text,

    parent_query = qt.text,

    program_name,

    host_name,

    nt_domain,

    es.last_request_start_time,

    DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration,

    NULL AS query_plan

FROM

    sys.dm_exec_sessions es

    INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id

    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt

WHERE

    ec.most_recent_session_id IN

    (

        SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)

    )

ORDER BY

    1, 2