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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.