-- ============================================================================
-- query-profile-diagnostics.sql
-- Find out which phase of the wall clock actually ate your Databricks query's
-- time -- queueing, cold compute, compilation, or execution -- plus a pruning
-- and shuffle check for the execution phase itself.
-- Source: Sqlism (sqlism.com) -- "How to Read a Databricks SQL Query Profile"
--
-- Requires: access to the system.query.history system table (enabled by
-- default on most workspaces; ask your workspace admin if it's missing).
-- Replace '<warehouse-id>' with your own SQL warehouse ID.
-- ============================================================================

-- 1) Phase breakdown for your slowest queries in the last 7 days -- start here.
--    total_duration_ms is the full statement time EXCLUDING result fetch time;
--    add result_fetch_duration_ms to get closer to true end-to-end wall clock.
SELECT
    statement_id,
    start_time,
    total_duration_ms,
    waiting_at_capacity_duration_ms,
    waiting_for_compute_duration_ms,
    compilation_duration_ms,
    execution_duration_ms,
    result_fetch_duration_ms,
    total_task_duration_ms
FROM system.query.history
WHERE compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
ORDER BY total_duration_ms DESC
LIMIT 50;

-- 2) How much of total_duration_ms is actually queueing/cold-start, versus
--    real planning + execution work? A warehouse that's frequently queued or
--    cold-starting will show large averages here relative to execution_duration_ms.
SELECT
    DATE(start_time) AS query_date,
    COUNT(*) AS total_queries,
    ROUND(AVG(waiting_at_capacity_duration_ms), 0) AS avg_waiting_at_capacity_ms,
    ROUND(AVG(waiting_for_compute_duration_ms), 0) AS avg_waiting_for_compute_ms,
    ROUND(AVG(compilation_duration_ms), 0)         AS avg_compilation_ms,
    ROUND(AVG(execution_duration_ms), 0)           AS avg_execution_ms,
    ROUND(AVG(total_duration_ms), 0)               AS avg_total_duration_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;

-- 3) Pruning and shuffle check for queries where execution_duration_ms
--    dominates the total: a low pruned-files ratio or a large shuffle_read_bytes
--    relative to produced_rows points at a file-layout or join-strategy problem
--    rather than a warehouse-sizing one.
SELECT
    statement_id,
    start_time,
    execution_duration_ms,
    read_files,
    pruned_files,
    ROUND(100.0 * pruned_files / NULLIF(pruned_files + read_files, 0), 1) AS pruned_files_pct,
    ROUND(shuffle_read_bytes / POWER(1024, 3), 2) AS shuffle_read_gb,
    produced_rows,
    read_io_cache_percent
FROM system.query.history
WHERE compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND execution_duration_ms > 0
ORDER BY execution_duration_ms DESC
LIMIT 50;