Difference between sys.dm_pdw_exec_* an sys.dm_pdw_nodes_exec_*

Dance, Cody R. (ALT) 30

I am trying to gather information on our use of synapse analytics and have run across two sets of dynamic management views (DMVs)- those of the form sys.dm_pdw_exec_* and those of the form sys.dm_pdw_nodes_exec_*.

For the purposes of this post, let's discuss sys.dm_pdw_exec_sessions and sys.dm_pdw_nodes_exec_sessions. In addition to having different information stored, these two views seem to be storing information about different sessions (or maybe different types of sessions?). This documentation provides guidance on how to link the tables (in the "Monitor tempdb" section), but this leads to totally mismatched login_name's from the two tables.

What's going on here? What is the difference between these two tables? Can they be linked?

QuantumCache 20,261 Reputation points

2023-11-17T20:56:50.5266667+00:00
Hello @Dance, Cody R. (ALT),

Thanks for the query,

I will try to debug on this scenario!

sys.dm_pdw_exec_sessions provides information about sessions at the distribution level,

while sys.dm_pdw_nodes_exec_sessions provides information about sessions at the node level. They can be linked together using the session_id column, but the login names may not match due to the different levels at which they are stored.

SELECT exec_sessions.session_id, exec_sessions.login_name AS control_node_login_name, nodes_sessions.login_name AS compute_node_login_name FROM sys.dm_pdw_exec_sessions AS exec_sessions JOIN sys.dm_pdw_nodes_exec_sessions AS nodes_sessions ON exec_sessions.session_id = nodes_sessions.session_id;

System views supported in Synapse SQL

System views for dedicated SQL pool (formerly SQL DW)
Dance, Cody R. (ALT) 30 Reputation points

2023-11-21T15:25:59.4066667+00:00

Hi @SatishBoddu-MSFT, thanks for the reply. In our tables, the session_id from sys.dm_pdw_exec_sessions is of the form "SID######" while the session_id from sys.dm_pdw_nodex_exec_sessions is an integer between 1 and 5000, so it doesn't seem to make sense to join on this key. The documentation linked in the original post suggests linking the sql_spid from the exec_sessions to the session_id of the nodes_sessions, but this creates the login name mismatch mentioned above.
QuantumCache 20,261 Reputation points

2023-11-27T03:55:04.11+00:00
Hello @Dance, Cody R. (ALT)

In this case, you can try to use the sql_spid column in sys.dm_pdw_exec_sessions to join with the session_id column in sys.dm_pdw_nodes_exec_sessions. However, as you mentioned, this may still result in a login name mismatch.

One possible solution to this issue is to use the session_context function to retrieve the login name for each session. Here's an example query that joins the two DMVs using sql_spid and session_id, and retrieves the login name using session_context: Give this a try!!

SELECT exec_sessions.sql_spid, exec_sessions.login_name AS control_node_login_name, nodes_sessions.login_name AS compute_node_login_name FROM sys.dm_pdw_exec_sessions AS exec_sessions JOIN sys.dm_pdw_nodes_exec_sessions AS nodes_sessions ON exec_sessions.sql_spid = session_context(N'wid') AND nodes_sessions.session_id = session_context(N'session_id');

Share via

Difference between sys.dm_pdw_exec_* an sys.dm_pdw_nodes_exec_*

Your answer