Skip to main content
PySparkSnowflakeDatabricks

SQL Endpoint Tuning — Query Performance Optimization

✨ Story Time — “Our Dashboards Are Slow Again…”​

Lena, a BI engineer, keeps hearing the same complaint:

“Tableau is loading too slow.”
“Power BI is timing out.”
“Why are queries taking forever?”

The data is clean.
The Delta tables are optimized.
But dashboards still feel sluggish.

Then she discovers the real culprit:

➡ The SQL Endpoint (SQL Warehouse) is not tuned properly.

After adjusting just a few settings, dashboards load 5× faster.

Let’s break down how she did it.

🧩 What Is a Databricks SQL Endpoint?​

A SQL Endpoint (now called SQL Warehouse) is a compute engine in Databricks dedicated to:

  • BI dashboards
  • Ad-hoc SQL queries
  • Reporting
  • Interactive analytics

It uses Photon by default (for fast SQL execution).

Tuning the SQL Warehouse is essential for:

  • Reducing dashboard load times
  • Preventing timeouts
  • Improving concurrency
  • Reducing compute cost

⚡ Key Areas of SQL Endpoint Tuning​

There are 5 major areas you must focus on:

  1. Warehouse Type
  2. Cluster Size & Scaling
  3. Caching Strategy
  4. Query Optimization
  5. Concurrency & Limits

Let’s explore each one.

🏗️ 1. Choosing the Right Warehouse Type​

Databricks offers:

🟩 Pro SQL Warehouse​

  • Fast
  • Photon-enabled
  • Great for most dashboards

🟦 Serverless SQL Warehouse​

  • Autoscaling
  • Zero management
  • Best for peak concurrency & BI tools

🟥 Classic SQL Warehouse (Deprecated)​

  • Avoid for new environments
  • Slower
  • Less optimized

Recommendation:
✔ Always choose Pro or Serverless
✔ Serverless is best for BI workloads

📏 2. Warehouse Size & Autoscaling​

If your dashboards are slow:

  • The warehouse may be too small
  • Or autoscaling is misconfigured

Best Practices:​

✔ Start small: Small or Medium
✔ Enable autoscaling
✔ Set min low and max slightly higher
✔ If concurrency is high → scale up, not out

Example config:


Min Size: Small
Max Size: Large
Scaling Mode: Auto

When to scale up:​

  • Large aggregations
  • Heavy joins
  • Many BI users at once

⚡ 3. Caching for Faster Queries​

SQL Warehouses use multiple caching layers:

✔ Query Result Cache​

Stores entire query results for repeated queries.

✔ Data Cache​

Caches table data on local SSD for faster scans.

✔ Metadata Cache​

Boosts table planning performance.

Best Practices:​

  • Ensure Photon is enabled
  • Use smaller, repeatable queries
  • Schedule regular OPTIMIZE + ZORDER jobs for data skipping

🔍 4. Query Optimization Techniques​

Even a perfectly tuned warehouse can be slowed down by a poorly written query.

Best Practices for SQL Tuning:​

🟩 Use SELECT only required columns
Avoid SELECT *

🟩 Filter early
Reduce data before joins:

WITH filtered AS (
  SELECT ...
  FROM table
  WHERE event_date >= current_date - 7
)

🟩 Use proper join types Avoid CROSS JOINs unless needed.

🟩 Avoid unnecessary nested subqueries

🟩 Use Delta Lake features

  • Z-ORDER by high-cardinality columns
  • OPTIMIZE for compaction

🟩 Use Photon-supported SQL functions Avoid Python UDFs.

👥 5. Concurrency & Resource Management​

Dashboards usually trigger dozens of queries at once.

To handle this:

🟩 Adjust concurrency settings​

Large BI teams? Increase max concurrency per warehouse.

🟩 Use Serverless for unpredictable workloads​

It scales instantly.

🟩 Monitor with Query Profile​

Identify slow operators:

  • Shuffle-heavy steps
  • Expensive joins
  • Broadcasts
  • Skewed partitions

🧪 Real-World Example — Faster Dashboards​

Before tuning:

  • Dashboards loading in 25 seconds
  • Concurrency errors
  • Warehouse running at 90% CPU

After tuning:

  • Switched to Serverless SQL Warehouse
  • Increased autoscaling range
  • Improved filtering + ZORDER
  • Enabled Photon + caching

Results:

  • Load time: 4 seconds
  • Compute cost: ↓ 27%
  • User satisfaction: ↑ 100%

🧠 Best Practices Summary​

🟩 Warehouse Tuning​

  • Use Pro or Serverless
  • Enable autoscaling
  • Choose correct size

🟩 Query Tuning​

  • Avoid SELECT *
  • Filter early
  • Use ZORDER & OPTIMIZE

🟩 Data Tuning​

  • Compact files
  • Use data skipping
  • Partition properly

🟩 BI Tuning​

  • Cache recurring queries
  • Avoid large extracts
  • Tune concurrency limits

📘 Summary​

  • SQL Endpoints (SQL Warehouses) power dashboards and analytic workloads.
  • Proper tuning drastically improves performance and reduces cost.
  • Photon, caching, autoscaling, and query optimization are the keys to fast BI.
  • With the right configuration, dashboards load in seconds, not minutes.

Your warehouse is the engine — tune it, and everything gets faster.

👉 Next Topic

Improving Lakehouse Performance — Dos & Don’ts