Query to check which User consuming more memory Usage Azure synapse dedicated SQL pool using SSMS
SELECT
ssu.session_id,
Sum( (es.memory_usage * 8) )AS 'MemoryUsage (in KB)',
exs.login_name AS 'LoginName' ,
exr.command
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
INNER JOIN adminpoc.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
group by ssu.session_id, exs.login_name , exr.command order by 2 desc
If want to exclude from users from list , Please use below Query:
SELECT
ssu.session_id,
Sum( (es.memory_usage * 8) )AS 'MemoryUsage (in KB)',
exs.login_name AS 'LoginName' ,
exr.command
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
INNER JOIN adminpoc.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
where exs.login_name <> 'abc_user'
group by ssu.session_id, exs.login_name , exr.command order by 2 desc