text: ﻿-- メモリ割り当ての確認
  -- https://docs.microsoft.com/ja-jp/azure/sql-data-warehouse/sql-data-warehouse-develop-concurrency#a-namememory-allocationaメモリの割り当て
  
  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
  