SELECT

-- SESSION_ID
------ ID of the session to which this request is related
	R.SESSION_ID,

-- BLOCKING_SESSION_ID
------ ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
------ -2 = The blocking resource is owned by an orphaned distributed transaction.
------ -3 = The blocking resource is owned by a deferred recovery transaction.
------ -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.
	R.BLOCKING_SESSION_ID,

-- REQUEST_START_TIME
------ Timestamp when the request arrived.
	R.start_time AS REQUEST_START_TIME,

	R.STATUS,
	S.HOST_NAME,
	CASE
		WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME
		ELSE S.LOGIN_NAME + ' (' + S.ORIGINAL_LOGIN_NAME + ')'
	END AS LOGIN_NAME,
	S.PROGRAM_NAME,
	DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,
	R.COMMAND,

-- CURRENT_WAIT_TYPE
------ If the request is currently blocked, this column returns the type of wait.
	R.WAIT_TYPE AS CURRENT_WAIT_TYPE,

-- WAIT_TIME
------ If the request is currently blocked, this column returns the duration in milliseconds, of the current wait.
	R.wait_time AS WAIT_TIME,

-- ROW_COUNT
------ Number of rows that have been returned to the client by this request.
	R.ROW_COUNT,

-- OPEN_TRANSACTION_COUNT
------ Number of transactions that are open for this request.
	R.OPEN_TRANSACTION_COUNT,

-- SESSION_REQUEST_ID
------ ID of the request. Unique in the context of the session.
	R.REQUEST_ID AS SESSION_REQUEST_ID,

-- LAST_WAIT_TYPE
------ If this request has previously been blocked, this column returns the type of the last wait.
	R.LAST_WAIT_TYPE,

-- LOCK_TIMEOUT
------ Lock time-out period in milliseconds for this request.
	R.lock_timeout AS LOCK_TIMEOUT,

-- DEADLOCK_PRIORITY
------ DEADLOCK_PRIORITY setting for the request.
	R.deadlock_priority AS DEADLOCK_PRIORITY,

-- TRANSACTION_ISOLATION_LEVEL
------ Isolation level with which the transaction for this request is created.
	CASE R.TRANSACTION_ISOLATION_LEVEL
		WHEN 0 THEN 'UNSPECIFIED'
		WHEN 1 THEN 'READUNCOMITTED'
		WHEN 2 THEN 'READCOMMITTED'
		WHEN 3 THEN 'REPEATABLE'
		WHEN 4 THEN 'SERIALIZABLE'
		WHEN 5 THEN 'SNAPSHOT'
		ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))
	END AS TRANSACTION_ISOLATION_LEVEL_NAME,

	R.USER_ID,
	C.CLIENT_NET_ADDRESS,
	ST.TEXT AS QUERY_TEXT,

-- START_OFFSET_QUERY
------ Number of characters into the currently executing batch or stored procedure at which the currently executing statement starts.
	R.statement_start_offset AS START_OFFSET_QUERY,

-- END_OFFSET_QUERY
------ Number of characters into the currently executing batch or stored procedure at which the currently executing statement ends.
	R.statement_end_offset AS END_OFFSET_QUERY,

	QP.QUERY_PLAN AS XML_QUERY_PLAN,

-- PERCENT_COMPLETE
------ Percentage of work completed for the following commands:
-------- ALTER INDEX REORGANIZE
-------- AUTO_SHRINK option with ALTER DATABASE
-------- BACKUP DATABASE
-------- DBCC CHECKDB
-------- DBCC CHECKFILEGROUP
-------- DBCC CHECKTABLE
-------- DBCC INDEXDEFRAG
-------- DBCC SHRINKDATABASE
-------- DBCC SHRINKFILE
-------- RECOVERY
-------- RESTORE DATABASE,
-------- ROLLBACK
-------- TDE ENCRYPTION
	R.PERCENT_COMPLETE
FROM
	SYS.DM_EXEC_REQUESTS R
LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S
	ON S.SESSION_ID = R.SESSION_ID
LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C
	ON C.CONNECTION_ID = R.CONNECTION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP
WHERE
	R.STATUS NOT IN ('BACKGROUND', 'SLEEPING')

--KILL <SESSION_ID>