Who is blocking me?

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!