DMV를 사용하여 Azure Synapse Analytics 전용 SQL 풀 워크로드 모니터링

아티클
03/10/2024

이 문서에서는 DMV(동적 관리 뷰)를 사용하여 전용 SQL 풀에서 쿼리 실행을 조사하는 작업을 비롯한 워크로드를 모니터링하는 방법을 설명합니다.

사용 권한

이 문서의 DMV를 쿼리하려면 VIEW DATABASE STATE 또는 CONTROL 권한이 필요합니다. 일반적으로 VIEW DATABASE STATE 권한 부여를 선호합니다. 훨씬 제한적이기 때문입니다.

GRANT VIEW DATABASE STATE TO myuser;

연결 모니터링

데이터 웨어하우스에 대한 모든 로그인이 sys.dm_pdw_exec_sessions에 기록됩니다. 이 DMV에는 마지막 10,000회의 로그인 정보가 포함됩니다. session_id는 기본 키로, 각각의 새 로그인에 대해 순차적으로 할당됩니다.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

쿼리 실행 모니터링

SQL 풀에 실행되는 모든 쿼리는 sys.dm_pdw_exec_requests에 기록됩니다. 이 DMV에는 마지막으로 실행한 쿼리 10,000개가 포함됩니다. request_id는 이 DMV의 기본 키인 request_id는 각 쿼리를 고유하게 식별합니다. request_id는 각각의 새 쿼리에 대해 순차적으로 할당되며 쿼리 ID를 나타내는 QID가 접두사로 추가됩니다. 이 DMV에서 지정된 session_id를 쿼리하면 지정된 로그온에 대한 모든 쿼리가 표시됩니다.

참고 항목

저장 프로시저는 여러 요청 ID를 사용합니다. 요청 ID는 순차적으로 할당됩니다.

특정 쿼리에 대한 쿼리 실행 계획 및 시간을 조사하기 위해 수행하는 단계는 다음과 같습니다.

1단계: 조사하려는 쿼리 식별

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

-- Find top 10 queries longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

위의 쿼리 결과에서 조사할 쿼리의 요청 ID를 적어 둡니다 .

일시 중단 상태의 쿼리는 실행 중인 활성 쿼리 수가 많기 때문에 대기할 수 있습니다. 이러한 쿼리는 sys.dm_pdw_waits에도 나타납니다. 이 경우 UserConcurrencyResourceType과 같은 대기를 찾습니다. 동시성 제한에 대한 자세한 내용은 메모리 및 동시성 제한 또는 워크로드 관리를 위한 리소스 클래스를 참조하세요. 쿼리는 개체 잠금 등의 기타 이유로 인해 대기 상태일 수도 있습니다. 쿼리가 리소스를 대기 중인 경우 이 문서 뒷부분의 리소스를 대기 중인 쿼리 조사 를 참조하세요.

sys.dm_pdw_exec_requests 테이블에서 쿼리 조회를 간소화하려면 LABEL을 사용하여 sys.dm_pdw_exec_requests 뷰에서 조회할 수 있는 주석을 쿼리에 할당합니다.

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

2단계: 쿼리 계획 조사

요청 ID를 사용하여 sys.dm_pdw_request_steps에서 쿼리의 DSQL(분산된 SQL) 계획을 검색합니다.

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

DSQL 계획의 시간이 생각보다 오래 걸리는 경우 계획이 여러 DSQL 단계를 포함하여 복잡하거나 한 단계에 시간이 오래 걸리는 것일 수 있습니다. 계획에 많은 단계가 포함되어 있으며 여러 이동 작업이 수행되는 경우에는 테이블 분산을 최적화하여 데이터 이동을 줄일 수 있습니다. 테이블 분산 조항은 쿼리를 해결하기 위해 데이터를 이동해야 하는 이유를 설명합니다. 이 문서에서는 데이터 이동을 최소화하기 위한 몇 가지 배포 전략에 대해서도 설명합니다.

단일 단계에 대한 추가 세부 정보를 조사하려면 장기 실행 쿼리 단계의 operation_type 열을 검사하고 단계 인덱스를 확인합니다.

SQL 작업(OnOperation, RemoteOperation, ReturnOperation)의 경우 3단계를 진행합니다.
데이터 이동 작업(ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation)의 경우 4단계를 진행합니다.

3단계: 분산 데이터베이스에서 SQL 조사

요청 ID와 단계 인덱스를 사용하여 sys.dm_pdw_sql_requests에서 세부 정보를 검색합니다. 이 보기에는 모든 분산 데이터베이스에 대한 쿼리 단계의 실행 정보가 포함되어 있습니다.

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

쿼리 단계가 실행되고 있으면 DBCC PDW_SHOWEXECUTIONPLAN을 사용하여 특정 분산에서 현재 실행 중인 단계에 대해 SQL Server 계획 캐시에서 SQL Server 예상 계획을 검색할 수 있습니다.

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

4단계: 분산 데이터베이스에서 데이터 이동 조사

요청 ID 및 단계 인덱스를 사용하여 sys.dm_pdw_dms_workers에서 각 분산에 대해 실행 중인 데이터 이동 단계에 대한 정보를 검색합니다.

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;

total_elapsed_time 열을 검사하여 특정 배포에서 데이터 이동 시간이 다른 배포보다 훨씬 오래 걸리는지 확인합니다.
장기 실행 배포의 경우 rows_processed 열을 검사하여 해당 배포에서 이동되는 행 수가 다른 배포보다 훨씬 큰지 확인합니다. 그렇다면 이 결과는 기본 데이터의 왜곡을 나타낼 수 있습니다. 데이터 기울이기의 한 가지 원인은 여러 NULL 값이 있는 열에 배포하기 때문입니다(행이 모두 동일한 배포에 배치됨). 이 유형의 열에 대한 배포를 방지하거나 가능한 경우 Null을 제거하도록 쿼리를 필터링하여 쿼리 속도 저하를 방지합니다.

쿼리가 실행되고 있으면 DBCC PDW_SHOWEXECUTIONPLAN을 사용하여 특정 배포 내에서 현재 실행 중인 SQL 단계에 대한 SQL Server 계획 캐시에서 SQL Server 예상 계획을 회수할 수 있습니다.

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

대기 중인 쿼리 모니터링

쿼리가 리소스를 대기하는 중이어서 진행되고 있지 않은 경우, 쿼리가 대기 중인 모든 리소스를 표시하는 쿼리는 다음과 같습니다.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,
      requests.command,
      requests.status,
      requests.start_time,
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

쿼리가 적극적으로 다른 쿼리의 리소스를 대기 중인 경우 상태는 AcquireResources입니다. 쿼리가 필요한 리소스를 모두 가지고 있으면 상태는 Granted입니다.

tempdb 모니터링

tempdb 데이터베이스는 쿼리를 실행하는 동안 중간 결과를 저장하는 데 사용됩니다. tempdb 데이터베이스의 사용률이 높으면 쿼리 성능이 저하될 수 있습니다. 구성된 모든 DW100c에 대해 399GB의 tempdb 공간이 할당됩니다(DW1000c에 3.99TB의 tempdb 공간이 있음). 다음은 tempdb 사용을 모니터링하고 쿼리에서 tempdb 사용을 줄이는 데 유용한 팁입니다.

뷰를 사용하여 tempdb 모니터링

tempdb 사용량을 모니터링하려면 먼저 Microsoft Toolkit for SQL 풀에서 microsoft.vw_sql_requests 뷰를 설치합니다. 그러면 다음 쿼리를 실행하여 실행된 모든 쿼리에 대한 노드당 tempdb 사용량을 확인할 수 있습니다.

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
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 DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

참고 항목

데이터 이동은 tempdb를 사용합니다. 데이터 이동 중에 tempdb의 사용을 줄이려면 테이블이 데이터를 균등하게 분산하는 분산 전략을 사용하고 있는지 확인합니다. Azure Synapse SQL 배포 관리자를 사용하여 워크로드에 적합한 배포 방법에 대한 권장 사항을 가져오세요. T-SQL 쿼리를 사용하여 tempdb를 모니터링하려면 Azure Synapse Toolkit을 사용합니다.

많은 양의 메모리를 사용하는 쿼리가 있거나 tempdb 할당과 관련된 오류 메시지를 받은 경우 최종 데이터 이동 작업에서 실패한 실행 중인 매우 큰 CREATE TABLE AS SELECT(CTAS) 또는 INSERT SELECT 문 때문일 수 있습니다. 이는 일반적으로 분산 쿼리 계획에서 최종 INSERT SELECT 바로 전에 ShuffleMove 작업으로 식별될 수 있습니다. sys.dm_pdw_request_steps를 사용하여 ShuffleMove 작업을 모니터링합니다.

가장 일반적인 완화 방법은 데이터 볼륨이 100DWUc당 399GB tempdb 제한을 초과하지 않도록 CTAS 또는 INSERT SELECT 문을 여러 로드 문으로 나누는 것입니다. 클러스터 크기를 더 큰 크기로 스케일링하여 tempdb 공간을 늘릴 수도 있습니다.

CTAS 및 INSERT SELECT 문 외에도 메모리 부족 상태에서 실행되는 복잡한 쿼리는 tempdb로 분할되어 쿼리가 실패할 수 있습니다. tempdb로 넘치지 않도록 더 큰 리소스 클래스를 사용하여 실행하는 것이 좋습니다.

메모리 모니터링

메모리는 성능 저하 및 메모리 부족 문제의 근본 원인일 수 있습니다. SQL Server 메모리 사용량이 쿼리 실행 중 한계에 도달한 것을 발견한 경우 데이터 웨어하우스를 확장하는 것이 좋습니다.

다음 쿼리는 노드당 SQL Server 메모리 사용량 및 메모리 부족을 반환합니다.

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

트랜잭션 로그 크기 모니터링

다음 쿼리는 각 배포에서 트랜잭션 로그 크기를 반환합니다. 로그 파일 중 하나가 160GB에 도달하는 경우 인스턴스를 확장하거나 트랜잭션 크기를 제한해야 합니다.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

트랜잭션 로그 롤백 모니터링

쿼리가 실패하거나 진행하는 데 시간이 오래 걸리는 경우 트랜잭션 롤백이 있는지 확인하고 모니터링할 수 있습니다.

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

PolyBase 부하 모니터링

다음 쿼리를 통해 예상되는 로드 진행률을 확인할 수 있습니다. 이 쿼리는 현재 처리 중인 파일만 표시합니다.

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

쿼리 차단 모니터링

다음 쿼리는 환경에서 차단된 상위 500개의 쿼리를 제공합니다.

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
FROM
    sys.dm_pdw_waits waiting
    INNER JOIN sys.dm_pdw_waits blocking
    ON waiting.object_type = blocking.object_type
    AND waiting.object_name = blocking.object_name
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

대기 중 및 차단 쿼리에서 쿼리 텍스트 검색

다음 쿼리는 쉽게 문제를 해결하기 위해 대기 및 차단 쿼리에 대한 쿼리 텍스트 및 식별자를 제공합니다.

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
FROM sys.dm_pdw_waits waiting
       INNER JOIN sys.dm_pdw_waits blocking
       ON waiting.object_type = blocking.object_type
       AND waiting.object_name = blocking.object_name
       INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
       ON blocking.request_id = blocking_exec_request.request_id
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

다음 단계

DMV에 대한 자세한 내용은 시스템 뷰를 참조하세요.