Understanding and Using SQL Server sys.dm_exec_requests

https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/ 

Find blocking / waiting:

SELECT session_id, blocking_session_id, start_time, wait_type, wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

Active query plan:

.SELECT DER.session_id, DEQP.query_plan 

FROM sys.dm_exec_requests AS DER
  CROSS APPLY sys.dm_exec_query_plan(DER.plan_handle) AS DEQP
WHERE NOT DER.status IN ('background', 'sleeping');

Get % complete

SELECT session_id, start_time, status, database_id,percent_complete 
FROM sys.dm_exec_requests

WHERE command = 'DBCC TABLE CHECK';

,

.