Query Optimisation: Query Profile and Performance Insights

    Who this is for:

    Architecture / Concept Overview: Query Optimisation: Query Profile and Performance Insights

    Every SQL statement executed on a SQL warehouse produces a query profile — a visual execution plan showing how Photon processed your query across its operators.

    %%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%% flowchart LR classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED SQL[SQL Statement] --> PARSE[Parse & Analyse] PARSE --> OPT[Catalyst Optimizer] OPT --> PLAN[Physical Plan] PLAN --> PHOTON[Photon Execution Engine] PHOTON --> SCAN[Scan Operators<br/>File pruning · Predicate pushdown] PHOTON --> JOIN[Join Operators<br/>Broadcast · Sort-merge · Shuffle hash] PHOTON --> AGG[Aggregate Operators<br/>Partial · Final aggregation] SCAN --> RESULT[Result Set] JOIN --> RESULT AGG --> RESULT SQL:::source PARSE:::ingestion OPT:::processing PLAN:::processing PHOTON:::processing SCAN:::storage JOIN:::processing AGG:::processing RESULT:::serving

    *Figure 1 — Query processing pipeline from SQL text through Catalyst optimisation to Photon execution operators.*

    The query profile reveals where time is spent across the execution tree.

    %%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%% graph TD classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED QP[Query Profile] --> METRICS[Key Metrics] METRICS --> DURATION[Total Duration<br/>Wall-clock time] METRICS --> ROWS[Rows Processed<br/>Per operator] METRICS --> SPILL[Spill to Disk<br/>Memory pressure indicator] METRICS --> BYTES[Bytes Read<br/>Scan efficiency] QP --> WARNINGS[Performance Warnings] WARNINGS --> SKEW[Data Skew Detected] WARNINGS --> CART[Cartesian Product Warning] WARNINGS --> NOPUSH[Predicate Not Pushed Down] QP:::governance METRICS:::processing DURATION:::processing ROWS:::processing SPILL:::ingestion BYTES:::storage WARNINGS:::source SKEW:::source CART:::source NOPUSH:::source

    *Figure 2 — Key metrics and warnings surfaced in a query profile.*

    Key Terms

    Prerequisites and Setup

    • A SQL warehouse (serverless or pro with Photon enabled)
    • Queries with measurable execution time (> 1 second) to profile
    • CAN USE privilege on the warehouse
    • Access to query history

    Step-by-Step Implementation

      Configuration Reference

      Query Optimisation: Query Profile and Performance Insights configuration options
      SettingScopeDefaultNotes
      PhotonWarehouseEnabled (serverless)Accelerates scan, join, and aggregation operators
      delta.tuneFileSizesForRewritesTablefalseEnables adaptive file sizing during OPTIMIZE
      Predictive optimisationSchema/CatalogInheritedAutomatically runs OPTIMIZE and VACUUM
      Broadcast thresholdSession10 MBTables below this size are automatically broadcast
      spark.sql.shuffle.partitionsSession200Tune for large-scale aggregations
      Result cacheWarehouseEnabledServes cached results for unchanged data

      Monitoring, Cost, and Security Considerations

      Common Pitfalls and Recommended Patterns

        Frequently Asked Questions