sp_who has always been a handy tool at the disposal of every DBA to quickly retrieve information about processes, sessions and users in SQL Server.
One of the typical uses was to run it to identify which processes are blocked by other executions and, more importantly, who is the blocker.
While Workload Management offers the ability to define importance and priority of running workloads, there could still be the chance of overlapping executions, especially if the SQL Pool instance is "open" to users for ad-hoc query execution.
It is usually easy to determine if your session is blocked, but it is not as easy to identify which session is the blocker.
A query like the following can help you identify blocked and blocking processes:
-- blocked session info
WITH blocked_sessions (login_name, blocked_session, state, type, command, object)
AS
(
SELECT
sessions.login_name,
blocked.session_id as blocked_session,
blocked.state ,
blocked.type,
requests.command,
blocked.object_name
FROM sys.dm_pdw_waits blocked
JOIN sys.dm_pdw_exec_requests requests
ON blocked.request_id = requests.request_id
JOIN sys.dm_pdw_exec_sessions sessions
ON blocked.session_id = sessions.session_id
WHERE blocked.state <> 'Granted'
)
--merging with blocking session info
SELECT
blocked_sessions.login_name as blocked_user,
blocked_sessions.blocked_session as blocked_session,
blocked_sessions.state as blocked_state,
blocked_sessions.type as blocked_type,
blocked_sessions.command as blocked_command,
sessions.login_name as blocking_user,
blocking.session_id as blocking_session,
blocking.state as blocking_state,
blocking.type as blocking_type,
requests.command as blocking_command
FROM sys.dm_pdw_waits blocking
JOIN blocked_sessions
ON blocked_sessions.object = blocking.object_name
JOIN sys.dm_pdw_exec_requests requests
ON blocking.request_id = requests.request_id
JOIN sys.dm_pdw_exec_sessions sessions
ON blocking.session_id = sessions.session_id
WHERE blocking.state = 'Granted'
Enjoy!
