Memory Usage in Azure synapse dedicated SQL pool

Posted by

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

guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x