Query Performance

WITH devalued AS (
  SELECT
    workspace_id,
    statement_id,
    statement_type,
    -- executed_by: partial-mask (email -> da****@****, service-principal GUID as-is, else first-2 + ****).
    CASE
      WHEN executed_by IS NULL OR executed_by = '__REDACTED__' THEN executed_by
      WHEN executed_by LIKE '%@%' THEN concat(substr(executed_by, 1, 2), '****@****')
      WHEN executed_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN executed_by
      ELSE concat(substr(executed_by, 1, 2), '****')
    END                                    AS executed_by,
    compute.warehouse_id                   AS warehouse_id,
    compute.type                           AS compute_type,
    start_time,
    execution_duration_ms,
    waiting_for_compute_duration_ms,
    total_task_duration_ms,
    read_bytes,
    read_files,
    pruned_files,
    read_partitions,
    read_rows,
    produced_rows,
    spilled_local_bytes,
    shuffle_read_bytes,
    from_result_cache,
    -- statement_text de-valued: strip emails, then replace every single-quoted literal with '?'
    -- (chr(39) is the single quote). Keeps the query shape, removes literal data values.
    regexp_replace(
      regexp_replace(statement_text, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,}', '<email>'),
      concat(chr(39), '[^', chr(39), ']*', chr(39)), '?'
    )                                      AS statement_text
  FROM system.query.history
  WHERE start_time >= dateadd(DAY, -:period_days, current_date())
    AND start_time <  current_date()
    AND execution_status = 'FINISHED'
    AND from_result_cache = false
    AND execution_duration_ms > 0
),
base AS (
  SELECT d.*, sha2(d.statement_text, 256) AS statement_fingerprint
  FROM devalued d
)
SELECT
  b.*,
  -- per-warehouse framing: this statement's share of its warehouse's total execution time.
  b.execution_duration_ms / NULLIF(SUM(b.execution_duration_ms) OVER (PARTITION BY b.warehouse_id), 0) AS pct_of_warehouse_exec_ms,
  ROW_NUMBER() OVER (PARTITION BY b.warehouse_id ORDER BY b.execution_duration_ms DESC)                 AS exec_rank_in_warehouse,
  -- status: worst-first band on within-warehouse execution share (field heuristic).
  CASE
    WHEN b.execution_duration_ms / NULLIF(SUM(b.execution_duration_ms) OVER (PARTITION BY b.warehouse_id), 0) >= :crit_wh_share THEN 'CRITICAL'
    WHEN b.execution_duration_ms / NULLIF(SUM(b.execution_duration_ms) OVER (PARTITION BY b.warehouse_id), 0) >= :warn_wh_share THEN 'WARN'
    ELSE 'OK'
  END AS status
FROM base b
ORDER BY b.execution_duration_ms DESC
LIMIT :top_n

SELECT
    workspace_id,
    statement_type,
    COUNT(*)                                   AS query_count,
    SUM(total_duration_ms) / 1000.0            AS total_duration_secs,
    SUM(COALESCE(total_task_duration_ms, 0)) / 1000.0 AS total_task_secs,
    COUNT(DISTINCT executed_by)                AS distinct_principals,
    MIN(start_time)                            AS first_query_time,
    MAX(start_time)                            AS last_query_time
FROM system.query.history
WHERE start_time >= dateadd(day, -:period_days, current_date())
  AND statement_text ILIKE '%databricks_audit%'
GROUP BY workspace_id, statement_type
ORDER BY workspace_id, statement_type

SELECT date(start_time) AS day, workspace_id, compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       COUNT(*) AS query_count,
       SUM(CASE WHEN from_result_cache THEN 1 ELSE 0 END) AS result_cache_hit_count,
       AVG(read_io_cache_percent) AS read_io_cache_percent_avg,
       SUM(CASE WHEN read_io_cache_percent < :warn_io_cache_pct THEN 1 ELSE 0 END) AS low_io_cache_count,
       SUM(waiting_for_compute_duration_ms) AS waiting_for_compute_ms_sum,
       SUM(compilation_duration_ms)         AS compilation_duration_ms_sum,
       -- status: worst-first band on average IO-cache hit rate (field heuristic; :warn_io_cache_pct / :crit_io_cache_pct).
       CASE
         WHEN AVG(read_io_cache_percent) IS NULL THEN 'NOT_ASSESSED'
         WHEN AVG(read_io_cache_percent) < :crit_io_cache_pct THEN 'CRITICAL'
         WHEN AVG(read_io_cache_percent) < :warn_io_cache_pct THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
GROUP BY date(start_time), workspace_id, compute.type, compute.warehouse_id
ORDER BY read_io_cache_percent_avg ASC

SELECT date(start_time) AS day, workspace_id, compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       execution_status, statement_type,
       CASE
         WHEN executed_by IS NULL OR executed_by = '__REDACTED__' THEN executed_by
         WHEN executed_by LIKE '%@%' THEN concat(substr(executed_by, 1, 2), '****@****')
         WHEN executed_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN executed_by
         ELSE concat(substr(executed_by, 1, 2), '****')
       END AS executed_by,
       COUNT(*) AS query_count,
       SUM(total_duration_ms)     AS total_duration_ms_sum,
       SUM(execution_duration_ms) AS execution_duration_ms_sum,
       -- error text de-valued at source: strip emails, then single-quoted string literals (chr(39) is
       -- the single quote) - keeps the error SHAPE, drops literal data values.
       regexp_replace(
         regexp_replace(MAX(error_message), '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,}', '<email>'),
         concat(chr(39), '[^', chr(39), ']*', chr(39)), '?'
       )                          AS error_message_sample,
       -- status: worst-first band on daily failed/canceled query count (field heuristic; :warn_failed_count / :crit_failed_count).
       CASE
         WHEN COUNT(*) >= :crit_failed_count THEN 'CRITICAL'
         WHEN COUNT(*) >= :warn_failed_count THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
  AND execution_status IN ('FAILED','CANCELED')
GROUP BY date(start_time), workspace_id, compute.type, compute.warehouse_id, execution_status, statement_type, executed_by
ORDER BY query_count DESC

SELECT date(start_time) AS day, workspace_id, compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       CASE
         WHEN executed_by IS NULL OR executed_by = '__REDACTED__' THEN executed_by
         WHEN executed_by LIKE '%@%' THEN concat(substr(executed_by, 1, 2), '****@****')
         WHEN executed_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN executed_by
         ELSE concat(substr(executed_by, 1, 2), '****')
       END AS executed_by,
       COUNT(*) AS spilling_query_count,
       SUM(spilled_local_bytes) AS spilled_local_bytes_sum,
       MAX(spilled_local_bytes) AS spilled_local_bytes_max,
       SUM(total_duration_ms)     AS total_duration_ms_sum,
       SUM(execution_duration_ms) AS execution_duration_ms_sum,
       SUM(shuffle_read_bytes)    AS shuffle_read_bytes_sum,
       -- status: worst-first band on daily local spill (field heuristic; :warn_spill_gb / :crit_spill_gb).
       CASE
         WHEN SUM(spilled_local_bytes) >= :crit_spill_gb * 1e9 THEN 'CRITICAL'
         WHEN SUM(spilled_local_bytes) >= :warn_spill_gb * 1e9 THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
  AND spilled_local_bytes > 0
GROUP BY date(start_time), workspace_id, compute.type, compute.warehouse_id, executed_by
ORDER BY spilled_local_bytes_sum DESC

SELECT date_trunc('HOUR', start_time) AS usage_hour, workspace_id,
       compute.warehouse_id AS warehouse_id, compute.type AS compute_type,
       CASE
         WHEN executed_by IS NULL OR executed_by = '__REDACTED__' THEN executed_by
         WHEN executed_by LIKE '%@%' THEN concat(substr(executed_by, 1, 2), '****@****')
         WHEN executed_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN executed_by
         ELSE concat(substr(executed_by, 1, 2), '****')
       END AS executed_by,
       statement_id, statement_type,
       execution_duration_ms, waiting_for_compute_duration_ms, total_task_duration_ms,
       read_bytes, total_duration_ms
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
  AND execution_status = 'FINISHED'
  AND from_result_cache = false
  AND execution_duration_ms > 0
  AND compute.warehouse_id IS NOT NULL
ORDER BY usage_hour, warehouse_id, statement_id

-- NEEDS CONFIRMATION: nested-struct dotted-path access + CASE attribution precedence are UNVERIFIED.
-- Confirm the struct path resolves and decide whether to emit ALL non-null source flags
-- rather than a single CASE winner (simultaneous population is documented).
SELECT workspace_id,
       compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       CASE WHEN executed_by IS NULL              THEN 'unknown'
            WHEN executed_by LIKE '%@%'           THEN 'user'
            ELSE 'service_principal' END AS identity_type,
       CASE WHEN executed_by IS NULL              THEN executed_by
            WHEN executed_by LIKE '%@%'           THEN concat(substr(executed_by, 1, 2), '****@****')
            ELSE executed_by END AS executed_by,
       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.notebook_id         IS NOT NULL THEN 'notebook'
            WHEN query_source.alert_id            IS NOT NULL THEN 'alert'
            WHEN query_source.genie_space_id      IS NOT NULL THEN 'genie'
            WHEN query_source.sql_query_id        IS NOT NULL THEN 'sql_editor'
            ELSE 'other' END AS source_kind,
       query_source.job_info.job_id AS job_id,
       query_source.dashboard_id    AS dashboard_id,
       query_source.notebook_id     AS notebook_id,
       COUNT(*) AS query_count,
       SUM(execution_duration_ms) AS execution_duration_ms_sum,
       SUM(total_duration_ms)     AS total_duration_ms_sum,
       SUM(read_bytes)            AS read_bytes_sum
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
GROUP BY workspace_id, compute.type, compute.warehouse_id, source_kind,
         CASE WHEN executed_by IS NULL              THEN 'unknown'
              WHEN executed_by LIKE '%@%'           THEN 'user'
              ELSE 'service_principal' END,
         CASE WHEN executed_by IS NULL              THEN executed_by
              WHEN executed_by LIKE '%@%'           THEN concat(substr(executed_by, 1, 2), '****@****')
              ELSE executed_by END,
         query_source.job_info.job_id, query_source.dashboard_id, query_source.notebook_id
ORDER BY workspace_id, compute_type, source_kind

SELECT date(start_time) AS day, workspace_id, compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       CASE
         WHEN executed_by IS NULL OR executed_by = '__REDACTED__' THEN executed_by
         WHEN executed_by LIKE '%@%' THEN concat(substr(executed_by, 1, 2), '****@****')
         WHEN executed_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN executed_by
         ELSE concat(substr(executed_by, 1, 2), '****')
       END AS executed_by, statement_type,
       COUNT(*) AS query_count,
       SUM(pruned_files)    AS pruned_files_sum,
       SUM(read_files)      AS read_files_sum,
       SUM(read_partitions) AS read_partitions_sum,
       SUM(read_bytes)      AS read_bytes_sum,
       SUM(read_rows)       AS read_rows_sum,
       -- status: worst-first band on the pruning ratio pruned/(pruned+read) (field heuristic; :warn_prune_ratio / :crit_prune_ratio).
       CASE
         WHEN SUM(pruned_files) + SUM(read_files) = 0 THEN 'NOT_ASSESSED'
         WHEN SUM(pruned_files) / (SUM(pruned_files) + SUM(read_files)) < :crit_prune_ratio THEN 'CRITICAL'
         WHEN SUM(pruned_files) / (SUM(pruned_files) + SUM(read_files)) < :warn_prune_ratio THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
  AND (pruned_files > 0 OR read_files > 0)
GROUP BY date(start_time), workspace_id, compute.type, compute.warehouse_id, executed_by, statement_type
ORDER BY (pruned_files_sum * 1.0 / NULLIF(pruned_files_sum + read_files_sum, 0)) ASC

SELECT date(start_time) AS day, workspace_id, compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       COUNT(*) AS query_count,
       SUM(CASE WHEN waiting_at_capacity_duration_ms > 0 THEN 1 ELSE 0 END) AS queued_at_capacity_count,
       SUM(CASE WHEN waiting_for_compute_duration_ms > 0 THEN 1 ELSE 0 END) AS waited_for_compute_count,
       SUM(waiting_at_capacity_duration_ms)  AS waiting_at_capacity_ms_sum,
       SUM(waiting_for_compute_duration_ms)  AS waiting_for_compute_ms_sum,
       SUM(total_duration_ms)                AS total_duration_ms_sum,
       -- status: worst-first band on combined queue + cold-start seconds (field heuristic; :warn_queue_secs / :crit_queue_secs).
       CASE
         WHEN SUM(waiting_at_capacity_duration_ms) + SUM(waiting_for_compute_duration_ms) >= :crit_queue_secs * 1000 THEN 'CRITICAL'
         WHEN SUM(waiting_at_capacity_duration_ms) + SUM(waiting_for_compute_duration_ms) >= :warn_queue_secs * 1000 THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
GROUP BY date(start_time), workspace_id, compute.type, compute.warehouse_id
ORDER BY (waiting_at_capacity_ms_sum + waiting_for_compute_ms_sum) DESC

SELECT date(start_time) AS day, workspace_id, compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       CASE
         WHEN executed_by IS NULL OR executed_by = '__REDACTED__' THEN executed_by
         WHEN executed_by LIKE '%@%' THEN concat(substr(executed_by, 1, 2), '****@****')
         WHEN executed_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN executed_by
         ELSE concat(substr(executed_by, 1, 2), '****')
       END AS executed_by,
       statement_type,
       COUNT(*) AS query_count,
       SUM(shuffle_read_bytes) AS shuffle_read_bytes_sum,
       SUM(written_bytes)      AS written_bytes_sum,
       SUM(written_rows)       AS written_rows_sum,
       SUM(written_files)      AS written_files_sum,
       SUM(read_bytes)         AS read_bytes_sum,
       -- status: worst-first band on shuffle volume OR small-file write amplification (field heuristic;
       -- :warn_shuffle_gb / :crit_shuffle_gb / :warn_avg_file_mb / :crit_avg_file_mb).
       CASE
         WHEN SUM(shuffle_read_bytes) >= :crit_shuffle_gb * 1e9
           OR (SUM(written_files) > 0 AND SUM(written_bytes) / SUM(written_files) < :crit_avg_file_mb * 1e6) THEN 'CRITICAL'
         WHEN SUM(shuffle_read_bytes) >= :warn_shuffle_gb * 1e9
           OR (SUM(written_files) > 0 AND SUM(written_bytes) / SUM(written_files) < :warn_avg_file_mb * 1e6) THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
  AND (shuffle_read_bytes > 0 OR written_bytes > 0)
GROUP BY date(start_time), workspace_id, compute.type, compute.warehouse_id, executed_by, statement_type
ORDER BY shuffle_read_bytes_sum DESC

SELECT date(start_time) AS day, workspace_id, hour(start_time) AS hour_of_day,
       compute.type AS compute_type, compute.warehouse_id AS warehouse_id,
       statement_type,
       CASE
         WHEN executed_by IS NULL OR executed_by = '__REDACTED__' THEN executed_by
         WHEN executed_by LIKE '%@%' THEN concat(substr(executed_by, 1, 2), '****@****')
         WHEN executed_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN executed_by
         ELSE concat(substr(executed_by, 1, 2), '****')
       END AS executed_by,
       COUNT(*) AS query_count,
       SUM(CASE WHEN execution_status = 'FINISHED' THEN 1 ELSE 0 END) AS finished_count,
       SUM(total_duration_ms)     AS total_duration_ms_sum,
       SUM(execution_duration_ms) AS execution_duration_ms_sum,
       SUM(read_bytes)            AS read_bytes_sum,
       SUM(produced_rows)         AS produced_rows_sum
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
  AND start_time < current_date()
GROUP BY date(start_time), workspace_id, hour(start_time), compute.type, compute.warehouse_id, statement_type, executed_by
ORDER BY day, hour_of_day, workspace_id, compute_type, warehouse_id

WITH devalued AS (
  SELECT
    statement_type,
    compute.warehouse_id AS warehouse_id,
    start_time,
    execution_duration_ms,
    regexp_replace(
      regexp_replace(statement_text, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,}', '<email>'),
      concat(chr(39), '[^', chr(39), ']*', chr(39)), '?'
    ) AS statement_text
  FROM system.query.history
  WHERE start_time >= dateadd(DAY, -:period_days, current_date())
    AND start_time <  current_date()
    AND execution_status = 'FINISHED'
    AND from_result_cache = false
    AND execution_duration_ms > 0
),
fp AS (
  SELECT sha2(statement_text, 256) AS statement_fingerprint, *
  FROM devalued
)
SELECT
  statement_fingerprint,
  MAX(statement_type)                       AS statement_type,
  min(statement_text)                        AS sample_statement_text,
  COUNT(*)                                   AS runs,
  SUM(execution_duration_ms)                 AS total_exec_ms,
  CAST(AVG(execution_duration_ms) AS BIGINT) AS avg_exec_ms,
  MAX(execution_duration_ms)                 AS max_exec_ms,
  COUNT(DISTINCT warehouse_id)               AS distinct_warehouses,
  MIN(start_time)                            AS first_seen,
  MAX(start_time)                            AS last_seen,
  -- status: worst-first band on cumulative execution time for the shape (field heuristic).
  CASE
    WHEN SUM(execution_duration_ms) >= :crit_total_hours * 3600 * 1000 THEN 'CRITICAL'
    WHEN SUM(execution_duration_ms) >= :warn_total_hours * 3600 * 1000 THEN 'WARN'
    ELSE 'OK'
  END AS status
FROM fp
GROUP BY statement_fingerprint
ORDER BY total_exec_ms DESC
LIMIT :top_n