text: /*
  	=========================
  	   View Workload Groups
  	=========================
  	The following query shows details of the memory resource allocation from the perspective of 
  	the resource governor. This is helpful for analyzing active and historic usage of the 
  	workload groups when troubleshooting.
  	
  	Source: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/analyze-your-workload
  */
  WITH rg
  AS
  (   SELECT  
       pn.name                                AS node_name
      ,pn.[type]                              AS node_type
      ,pn.pdw_node_id                         AS node_id
      ,rp.name                                AS pool_name
      ,rp.max_memory_kb*1.0/1024              AS pool_max_mem_MB
      ,wg.name                                AS group_name
      ,wg.importance                          AS group_importance
      ,wg.request_max_memory_grant_percent    AS group_request_max_memory_grant_pcnt
      ,wg.max_dop                             AS group_max_dop
      ,wg.effective_max_dop                   AS group_effective_max_dop
      ,wg.total_request_count                 AS group_total_request_count
      ,wg.total_queued_request_count          AS group_total_queued_request_count
      ,wg.active_request_count                AS group_active_request_count
      ,wg.queued_request_count                AS group_queued_request_count
      FROM    sys.dm_pdw_nodes_resource_governor_workload_groups wg
      JOIN    sys.dm_pdw_nodes_resource_governor_resource_pools rp    
              ON  wg.pdw_node_id  = rp.pdw_node_id
              AND wg.pool_id      = rp.pool_id
      JOIN    sys.dm_pdw_nodes pn
              ON    wg.pdw_node_id    = pn.pdw_node_id
      WHERE   wg.name like 'SloDWGroup%'
      AND     rp.name    = 'SloDWPool'
  )
  SELECT  pool_name
  ,       pool_max_mem_MB
  ,       group_name
  ,       group_importance
  ,       (pool_max_mem_MB/100)*group_request_max_memory_grant_pcnt AS max_memory_grant_MB
  ,       node_name
  ,       node_type
  ,       group_total_request_count
  ,       group_total_queued_request_count
  ,       group_active_request_count
  ,       group_queued_request_count
  FROM    rg
  ORDER BY
          node_name
  ,       group_request_max_memory_grant_pcnt
  ,       group_importance
  ;