SQL 웨어하우스 작업 모니터링에 대한 예제 쿼리

시스템 테이블과 함께 이러한 예제 SQL 쿼리를 사용하여 SQL 웨어하우스 성능, 사용량 및 비용을 모니터링합니다. 조직의 요구에 맞게 쿼리를 수정합니다. 예기치 않은 값에 대한 알림을 받으려면 경고를 추가합니다.

요구 사항

• 시스템 테이블에 액세스할 수 있어야 합니다. 요구 사항은 시스템 테이블을 사용하여 계정 활동 모니터링 을 참조하세요.
• 대부분의 시스템 테이블에는 Unity 카탈로그를 사용하도록 설정된 계정이 필요합니다.

SQL 웨어하우스 모니터링을 위한 테이블

시스템 테이블	설명
system.compute.warehouse_events	웨어하우스 시작, 중지, 강화 및 규모 축소 이벤트를 추적합니다.
system.compute.warehouses	웨어하우스 구성의 스냅샷을 포함합니다.
system.query.history	SQL 웨어하우스에서 실행되는 모든 쿼리에 대한 세부 정보를 기록합니다.
system.billing.usage	모든 Azure Databricks 사용에 대한 청구 레코드를 포함합니다.

예: 웨어하우스 사용량

다음 쿼리를 사용하여 가장 많은 작업을 구동하는 쿼리, 사용자 및 애플리케이션을 포함하여 웨어하우스가 사용되는 방식을 이해합니다.

웨어하우스에서 가장 느린 쿼리 찾기

SELECT
  statement_id,
  executed_by,
  statement_type,
  execution_status,
  total_duration_ms,
  execution_duration_ms,
  compilation_duration_ms,
  waiting_at_capacity_duration_ms,
  read_rows,
  produced_rows,
  start_time,
  statement_text
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 1 DAY
ORDER BY
  total_duration_ms DESC
LIMIT 50

시간에 따른 쿼리 성능 추세 분석

SELECT
  DATE(start_time) AS query_date,
  COUNT(*) AS total_queries,
  COUNT(CASE WHEN execution_status = 'FINISHED' THEN 1 END) AS successful_queries,
  COUNT(CASE WHEN execution_status = 'FAILED' THEN 1 END) AS failed_queries,
  ROUND(AVG(total_duration_ms), 0) AS avg_duration_ms,
  ROUND(PERCENTILE(total_duration_ms, 0.5), 0) AS p50_duration_ms,
  ROUND(PERCENTILE(total_duration_ms, 0.95), 0) AS p95_duration_ms,
  ROUND(AVG(waiting_at_capacity_duration_ms), 0) AS avg_queue_wait_ms
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 30 DAY
GROUP BY
  DATE(start_time)
ORDER BY
  query_date DESC

웨어하우스에서 가장 활동적인 사용자 찾기

SELECT
  executed_by,
  COUNT(*) AS query_count,
  ROUND(SUM(total_duration_ms) / 1000 / 60, 2) AS total_duration_minutes,
  ROUND(AVG(total_duration_ms), 0) AS avg_duration_ms
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
GROUP BY
  executed_by
ORDER BY
  query_count DESC

상위 클라이언트 애플리케이션 찾기

SELECT
  client_application,
  CASE
    WHEN query_source.job_info.job_id IS NOT NULL THEN 'Job'
    WHEN query_source.dashboard_id IS NOT NULL THEN 'Dashboard'
    WHEN query_source.legacy_dashboard_id IS NOT NULL THEN 'Legacy Dashboard'
    WHEN query_source.alert_id IS NOT NULL THEN 'Alert'
    WHEN query_source.notebook_id IS NOT NULL THEN 'Notebook'
    WHEN query_source.genie_space_id IS NOT NULL THEN 'Genie Space'
    WHEN query_source.sql_query_id IS NOT NULL THEN 'SQL Editor'
    ELSE 'Other'
  END AS source_type,
  COUNT(*) AS query_count,
  ROUND(AVG(total_duration_ms), 0) AS avg_duration_ms
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
GROUP BY
  client_application,
  source_type
ORDER BY
  query_count DESC

실패한 쿼리 모니터링

SELECT
  DATE(start_time) AS failure_date,
  execution_status,
  error_message,
  COUNT(*) AS failure_count,
  COLLECT_SET(executed_by) AS affected_users
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND execution_status IN ('FAILED', 'CANCELED')
  AND start_time >= NOW() - INTERVAL 7 DAY
GROUP BY
  DATE(start_time),
  execution_status,
  error_message
ORDER BY
  failure_date DESC,
  failure_count DESC

예: 웨어하우스 크기 조정

다음 쿼리를 사용하여 웨어하우스의 크기가 올바른지 여부를 확인합니다. 용량이 한계에 도달하여 대기 중인 쿼리는 max_clusters을(를) 늘려야 함을 제안합니다. 디스크 유출이 과도한 쿼리는 웨어하우스 크기를 늘려야 한다고 제안합니다.

시스템 용량 한도에서 대기 중인 쿼리 식별

값이 높은 waiting_at_capacity_duration_ms 쿼리는 실행하는 대신 대기하는 데 시간을 소비합니다. 웨어하우스 max_clusters 설정을 확장하여 웨어하우스가 확장할 수 있도록 고려하는 것이 좋습니다.

SELECT
  statement_id,
  executed_by,
  total_duration_ms,
  waiting_at_capacity_duration_ms,
  execution_duration_ms,
  start_time,
  statement_text
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND waiting_at_capacity_duration_ms > 0
ORDER BY
  waiting_at_capacity_duration_ms DESC
LIMIT 50

과도한 디스크 유출로 쿼리 식별

디스크 유출은 쿼리에 사용 가능한 메모리보다 더 많은 메모리가 필요할 때 발생합니다. 쿼리에 더 많은 메모리를 제공하도록 웨어하우스 크기를 늘리는 것이 좋습니다. 과도한 유출은 일반적으로 쿼리에 최적화가 필요하거나 웨어하우스 크기가 워크로드에 비해 너무 작다는 것을 의미합니다.

SELECT
  statement_id,
  executed_by,
  spilled_local_bytes / (1024 * 1024) AS spilled_mb,
  read_bytes / (1024 * 1024) AS read_mb,
  total_duration_ms,
  start_time,
  statement_text
FROM
  system.query.history
WHERE
  compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND spilled_local_bytes > 0
ORDER BY
  spilled_local_bytes DESC
LIMIT 50

예: 웨어하우스 비용

다음 쿼리를 사용하여 SQL 웨어하우스와 관련된 비용을 이해하고 추적합니다.

일별 웨어하우스 비용 모니터링

SELECT
  usage_date,
  sku_name,
  ROUND(SUM(usage_quantity), 2) AS total_dbus,
  ROUND(SUM(usage_quantity * list_prices.pricing.default), 2) AS estimated_list_cost
FROM
  system.billing.usage
  LEFT JOIN system.billing.list_prices ON usage.sku_name = list_prices.sku_name
    AND price_end_time IS NULL
WHERE
  usage_metadata.warehouse_id = '<warehouse-id>'
  AND usage_date >= NOW() - INTERVAL 30 DAY
GROUP BY
  usage_date,
  sku_name
ORDER BY
  usage_date DESC

웨어하우스 이벤트와 쿼리 볼륨의 상관 관계 지정

이 쿼리는 웨어하우스 크기 조정 이벤트와 쿼리 작업 간의 관계를 파악하여 비용 최적화 기회를 식별하는 데 도움이 됩니다.

WITH hourly_events AS (
  SELECT
    DATE_TRUNC('hour', event_time) AS event_hour,
    warehouse_id,
    MAX(cluster_count) AS max_clusters,
    COLLECT_SET(event_type) AS event_types
  FROM
    system.compute.warehouse_events
  WHERE
    warehouse_id = '<warehouse-id>'
    AND event_time >= NOW() - INTERVAL 7 DAY
  GROUP BY
    DATE_TRUNC('hour', event_time),
    warehouse_id
),
hourly_queries AS (
  SELECT
    DATE_TRUNC('hour', start_time) AS query_hour,
    COUNT(*) AS query_count,
    ROUND(AVG(total_duration_ms), 0) AS avg_duration_ms,
    ROUND(AVG(waiting_at_capacity_duration_ms), 0) AS avg_queue_wait_ms
  FROM
    system.query.history
  WHERE
    compute.warehouse_id = '<warehouse-id>'
    AND start_time >= NOW() - INTERVAL 7 DAY
  GROUP BY
    DATE_TRUNC('hour', start_time)
)
SELECT
  COALESCE(e.event_hour, q.query_hour) AS hour,
  q.query_count,
  q.avg_duration_ms,
  q.avg_queue_wait_ms,
  e.max_clusters,
  e.event_types
FROM
  hourly_events e
  FULL OUTER JOIN hourly_queries q ON e.event_hour = q.query_hour
ORDER BY
  hour DESC