Synapse Analytics Dedicated SQL Pool memory consumption by query

Question

How can I monitor the memory consumption for every single running query?

Answer 1

Hello anonymous user,

Yes, you can see the individual session_id memory consumption details using DMVs.

sys.dm_pdw_nodes_exec_sessions holds the memory usage information. You can join with other DMVs to get the information

SELECT  
ssu.session_id,  
Sum( (es.memory_usage * 8) )AS 'MemoryUsage (in KB)',  
exs.login_name AS 'LoginName'  
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 microsoft.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 es.is_user_process = 1  
group by ssu.session_id, exs.login_name   

To create the microsoft.vw_sql_requests view

create schema Microsoft   
Go  

CREATE VIEW microsoft.vw_sql_requests  
AS  
(  
 SELECT  
 sr.request_id,  
 sr.step_index,  
 (CASE WHEN (sr.distribution_id = -1 ) THEN (SELECT pdw_node_id FROM sys.dm_pdw_nodes WHERE type = 'CONTROL') ELSE d.pdw_node_id END) AS pdw_node_id,  
 sr.distribution_id,  
 sr.status,  
 sr.error_id,  
 sr.start_time,  
 sr.end_time,  
 sr.total_elapsed_time,  
 sr.row_count,  
 sr.spid,  
 sr.command  
 FROM  
 sys.pdw_distributions AS d  
 RIGHT JOIN sys.dm_pdw_sql_requests AS sr ON d.distribution_id = sr.distribution_id  
)  
GO  

reference documents:
https://github.com/Microsoft/sql-data-warehouse-samples/blob/main/solutions/monitoring/scripts/views/microsoft.vw_sql_requests.sql
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor

------------------------------

If this answers your question, please consider accepting the answer by hitting the Accept answer button, as it helps the community.