SELECT * 
FROM   sys.dm_exec_connections
       OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle);

SELECT * 
FROM   sys.dm_exec_sessions AS s
       LEFT OUTER JOIN sys.dm_exec_connections AS c
          ON s.session_id = c.session_id
       CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle);
       

SELECT * 
FROM   sys.dm_exec_sessions AS s
       LEFT OUTER JOIN sys.dm_exec_connections AS c
          ON s.session_id = c.session_id
       LEFT OUTER JOIN sys.dm_exec_requests AS r
          ON s.session_id = r.session_id
       OUTER APPLY sys.dm_exec_sql_text(sql_handle)
       OUTER APPLY sys.dm_exec_query_plan(plan_handle);

--> bloqueurs et bloqués
SELECT * 
FROM   sys.dm_exec_sessions AS s
       LEFT OUTER JOIN sys.dm_exec_connections AS c
          ON s.session_id = c.session_id
       LEFT OUTER JOIN sys.dm_exec_requests AS r
          ON s.session_id = r.session_id
       OUTER APPLY sys.dm_exec_sql_text(sql_handle)
       OUTER APPLY sys.dm_exec_query_plan(plan_handle)
WHERE  s.session_id  IN (SELECT blocking_session_id
                      FROM   sys.dm_exec_requests AS r 
                      WHERE  blocking_session_id > 0)
UNION ALL
SELECT * 
FROM   sys.dm_exec_sessions AS s
       LEFT OUTER JOIN sys.dm_exec_connections AS c
          ON s.session_id = c.session_id
       LEFT OUTER JOIN sys.dm_exec_requests AS r
          ON s.session_id = r.session_id
       OUTER APPLY sys.dm_exec_sql_text(sql_handle)
       OUTER APPLY sys.dm_exec_query_plan(plan_handle)
WHERE  blocking_session_id <> 0;