Stephan Köppen

With APS V2 Microsoft implemented a feature called workload management. The whole purpose of this functionality is to assign a specific amount of memory and there a certain amount of concurrent slots to logins. Whenever this particular login connects to PDW and executes sql statements, it is done with the resource class. But keep in mind: PDW only has 32 concurrent slots, so choose your classes wisely. Below you will find an overview of the different classes.

 

The „Maximum Memory Usage“ is an approximation and will be allocated for each distribution. So on one node a default query will request 400MB * 8 Nodes = 3200MB of memory.
When changing resource classes, we have to differ between the scenarios
  1. Queries which require more memory (hash-joins in general)
  2. Heavy CTAS operations with lots of aggregations and joins

So lets check the different scenarios.

1) Generally speaking it is not that easy to find a query which needs more memory, than what is default wise allocated. If you think you a have a quite heavy query with plenty of joins (preferably hash-joins), group by and order by parts, you can easily check the consumption including the gap with this query
SELECT
pr.request_id
, [mem].granted_memory_kb
, [mem].requested_memory_kb
, [mem].ideal_memory_kb
, ( [mem].ideal_memory_kb – [mem].granted_memory_kb )/( 1024 * 1024 ) AS requested_memory_gap
FROM sys.dm_pdw_exec_requests AS pr
JOIN sys.dm_pdw_sql_requests AS psqlr ON psqlr.request_id = pr.request_id
JOIN sys.dm_pdw_nodes_exec_requests AS sqlr ON sqlr.[session_id] = psqlr.[spid]
AND sqlr.[pdw_node_id] = psqlr.[pdw_node_id]
JOIN sys.dm_pdw_nodes_exec_query_memory_grants AS [mem] ON [mem].[session_id] = psqlr.[spid]
AND [mem].[pdw_node_id] = psqlr.[pdw_node_id]
2) When rebuilding a table, or creating a new table based on certain fact tables, it might occur that the resources aren’t enough. Especially when you also compress the whole generated data in a clustered columnstore index. Choosing a larger resource class might help saving valuable time by accelerating the task.

 

Generally speaking i haven’t found any task yet, where changing the resource class boosted anything. This might be due to pretty straight forward select statements without heavy aggregation, as well as the fact, that when we load into our table we also load whole partitions which are switched in and out.  But in case you wonder, take the above query and see yourself if you do have the need to adjust the classes.