Contoh kueri untuk memantau aktivitas gudang SQL

Gunakan contoh kueri SQL ini dengan tabel sistem untuk memantau performa, penggunaan, dan biaya gudang SQL. Ubah kueri agar sesuai dengan kebutuhan organisasi Anda. Tambahkan pemberitahuan untuk mendapatkan pemberitahuan tentang nilai tak terduga.

Persyaratan

  • Anda harus memiliki akses ke tabel sistem. Lihat Referensi tabel sistem untuk persyaratan.
  • Sebagian besar tabel sistem mengharuskan akun mengaktifkan Katalog Unity.

Tabel untuk pemantauan gudang SQL

Tabel sistem Deskripsi
system.compute.warehouse_events Melacak peristiwa mulai, berhenti, peningkatan, dan penurunan skala gudang.
system.compute.warehouses Berisi rekam jepret konfigurasi gudang.
system.query.history Merekam detail tentang setiap kueri yang dijalankan di gudang SQL.
system.billing.usage Berisi rekaman penagihan untuk semua penggunaan Azure Databricks.

Contoh: Penggunaan gudang

Gunakan kueri berikut untuk memahami bagaimana gudang Anda digunakan, termasuk kueri, pengguna, dan aplikasi mana yang paling banyak mendorong aktivitas.

Cari kueri yang paling lambat di gudang.

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

Menemukan pengguna paling aktif di gudang

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

Menemukan aplikasi klien teratas

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.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

Memantau kueri yang gagal

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

Contoh: Ukuran gudang

Gunakan kueri berikut untuk menentukan apakah gudang Anda berukuran benar. Kueri yang tertahan karena kapasitas menunjukkan bahwa Anda perlu meningkatkan max_clusters. Kueri dengan kelebihan penumpukan data pada disk menunjukkan bahwa Anda perlu meningkatkan kapasitas gudang data.

Mengidentifikasi kueri yang tertunda pada kapasitas penuh

Kueri dengan nilai tinggi waiting_at_capacity_duration_ms menghabiskan waktu yang diantrekan alih-alih berjalan. Pertimbangkan untuk meningkatkan pengaturan gudang max_clusters untuk memungkinkan gudang meningkatkan kapasitas.

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

Identifikasi kueri yang mengalami tumpahan disk yang berlebihan

Tumpahan disk terjadi ketika kueri memerlukan lebih banyak memori daripada yang tersedia. Pertimbangkan untuk meningkatkan ukuran gudang untuk memberikan lebih banyak memori pada kueri. Tumpahan data yang berlebihan biasanya berarti kueri membutuhkan pengoptimalan atau bahwa ukuran gudang terlalu kecil untuk beban kerja.

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

Contoh: Biaya gudang

Gunakan kueri berikut untuk memahami dan melacak biaya yang terkait dengan gudang SQL Anda.

Memantau biaya gudang per hari

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

Mengaitkan peristiwa gudang dengan volume kueri

Kueri ini membantu Anda memahami hubungan antara peristiwa penskalaan gudang dan aktivitas kueri untuk mengidentifikasi peluang pengoptimalan biaya.

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
  • Last updated on