Databricks SQL Warehouse Tuning: Concurrency, Caching, and Sizing

It is tempting to assume "a bigger SQL Warehouse is always faster," but in practice you need to understand three levers — size, cluster count, and cache settings — or you will just inflate costs without gaining performance. This article walks through sizing strategy, concurrency control, how each cache layer behaves, and a concrete tuning workflow.

SQL Warehouse Sizing Reference

SQL Warehouse size determines how much compute each cluster gets. Larger sizes process individual queries faster, but DBU cost scales proportionally.

Adding clusters scales out concurrency, but each cluster maintains its own Disk Cache, so cache hit rate can drop. Use sizing up (scale up) to make a single query faster, and adding clusters (scale out) to handle more concurrent queries.

Concurrency and Query Queuing Mitigation

When resources run out, the SQL Warehouse queues incoming queries. If a dashboard load triggers many queries at once, Queuing Time dominates the total latency and user experience suffers significantly.

Typical patterns that trigger queuing

• Automatic dashboard refreshes fire dozens or hundreds of queries simultaneously
• BI tools (Tableau / Power BI) issue queries in parallel
• Multiple users run ad-hoc queries right after an ETL job completes

Mitigation priorities

1. Set the scaling policy to "Optimized" so clusters auto-scale with demand
2. Account for warm-up time and set Min Clusters to 1 or more to avoid cold starts
3. Set Max Clusters appropriately to cap the upper cost bound
4. Tune dashboard refresh intervals to cut down unnecessary concurrency
5. Split Warehouses by workload (heavy ad-hoc vs. lightweight dashboards)

Result Cache

Result Cache returns the previous result set whenever the exact same SQL text runs again. It skips compilation and data scanning entirely, making it the fastest cache layer.

When the cache is valid

• The SQL text matches exactly (even whitespace differences disqualify it)
• The underlying table data has not changed (detected via Delta Log)
• The query does not include non-deterministic functions like CURRENT_TIMESTAMP() or rand()
• It is within the cache TTL (24 hours by default)

For workloads that run the same query repeatedly — like dashboards — Result Cache is extremely effective. It does not help with ad-hoc queries whose WHERE clause parameters change every time.

Disk Cache (SSD Cache)

Disk Cache stores data read from remote storage (S3/ADLS/GCS) on the local SSDs of Warehouse nodes. On the second and subsequent queries, scans hit local SSDs instead of going over the network, which significantly improves scan speed.

• Cached at the granularity of individual Parquet files in a Delta table
• Running OPTIMIZE or VACUUM on a table changes its files, which invalidates the cache
• Stopping or restarting the Warehouse clears the Disk Cache
• A longer Auto Stop timeout helps you keep cache benefits but increases idle cost — it is a tradeoff

Predictive I/O

Predictive I/O learns from historical query patterns and prefetches data Databricks expects you to need next. It uses table statistics and query history to fetch files that match your filter conditions ahead of time.

• Automatically enabled on Pro / Serverless Warehouses
• Especially effective for range filters on large tables (e.g., date ranges)
• Combined with Disk Cache, even first-run queries can approach cached-query speeds

IWM (Intelligent Workload Management)

IWM is available on Pro / Serverless Warehouses and dynamically allocates Warehouse resources per query. Classic Warehouses assign queries to fixed slots; IWM gives small queries fewer resources and large queries more, automatically.

5-Step Tuning Workflow

Here is the recommended sequence we use in production to improve SQL Warehouse performance.

Step 1: Classify your workload

First, classify queries into "dashboard" (same query repeated), "ad-hoc" (different every time), and "ETL validation" (heavy joins and aggregations). Isolate workloads with very different profiles onto separate Warehouses.

Step 2: Pick an initial size

Use Query Profile to inspect scan volume and execution time, then pick an initial size based on the table above. When in doubt, start small and step up gradually — it is the safer path.

Step 3: Configure scaling

Set Scaling Policy to Optimized, Min Clusters to 1 (to avoid cold starts), and Max Clusters based on peak concurrency.

Step 4: Maximize cache efficiency

Set Auto Stop to around 10 minutes to retain Disk Cache while balancing idle cost. For dashboard Warehouses, monitor Result Cache hit rate and consider removing non-deterministic functions and standardizing query text.

Step 5: Monitor and iterate

Regularly review Queuing Time, Execution Time, and Rows Scanned in Query History. If Queuing Time exceeds 10% of total, add clusters. If Execution Time is long, size up. If Rows Scanned is excessive, optimize the query (partitioning, Z-ORDER, additional filters).

Key Points the Exam Tests

• "What is the difference between Result Cache and Disk Cache?" → Result Cache reuses result sets; Disk Cache is an SSD cache of data files
• "What to do when Queuing Time is high" → Adding clusters (scaling out) is the top priority
• "Why a query got slower after running OPTIMIZE on a table" → Because Disk Cache was invalidated
• "Should I size up or add clusters?" → Size up if a single query is slow; add clusters if concurrency causes waiting

Check with a Sample Question

Frequently Asked Questions