SQLDW のワークロードの管理.sql

-- https://azure.microsoft.com/ja-jp/documentation/articles/sql-data-warehouse-develop-concurrency/


-- ワークロードグループのリソースと使用状況の確認
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

-- ロールとメンバーの管理。ロールに追加されていない場合は、smallrc で動作している
SELECT  r.name AS role_principal_name
,       m.name AS member_principal_name
FROM    sys.database_role_members rm
JOIN    sys.database_principals AS r            ON rm.role_principal_id     = r.principal_id
JOIN    sys.database_principals AS m            ON rm.member_principal_id   = m.principal_id
WHERE   r.name IN ('mediumrc','largerc', 'xlargerc')


SELECT   r.[request_id]                                 AS Request_ID
        ,r.[status]                                     AS Request_Status
        ,r.[submit_time]                                AS Request_SubmitTime
        ,r.[start_time]                                 AS Request_StartTime
        ,DATEDIFF(ms,[submit_time],[start_time])        AS Request_InitiateDuration_ms
        ,r.resource_class                               AS Request_resource_class
FROM    sys.dm_pdw_exec_requests r