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    sessUNION 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_planFROM    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 qtWHERE    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

 

http://www.cnblogs.com/double-K/p/6264814.html