Retrieving the database user for a request exposed by the sys.dm_pdw_exec_requests DMV

Question

Hi,

during a monitoring activity for a Synapse dedicated SQL pool using the sys.dm_pdw_exec_requests DMV I've noticed an unexpected resource class and so I need to retrieve the corresponding database user.

The sys.dm_pdw_exec_requests DMV esposes some data which request_id, session_id, total_elapsed_time, resource_class, group_name and classifier_name, but not an useful id to retrieve the related database user.

Any suggests to me, please?

Answer 1

Hi pmscorca,

Thanks for reaching out to Microsoft Q&A.

You can by joining the 'sys.dm_pdw_exec_requests' view with the 'sys.dm_pdw_exec_sessions' view using the session_id column. This will let you link the request_id to the session under which it was executed.

Go ahead and select the relevant columns from both views. Specifically, you will need the session_id (associated with the session), the command (which holds the full text of the request submitted by the user), and any other relevant information. Or you can simply use the following query to pull the active or running connections with the database user_id.

SELECT
    r.session_id,
    s.login_name AS 'User ID',
    r.command
FROM sys.dm_pdw_exec_requests AS r
JOIN sys.dm_pdw_exec_sessions AS s ON r.session_id = s.session_id
WHERE r.status NOT IN ('Completed', 'Failed', 'Cancelled')

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.