4.3.4. Data Warehouse Query Optimization
💡 First Principle: Warehouse query performance depends on statistics accuracy, caching, and query structure. The query optimizer makes execution plan decisions based on statistics—outdated statistics lead to suboptimal plans, like navigating with a map from last year. Without proper optimization, queries that should take seconds take minutes as data grows.
Scenario: A reporting query joins three large tables and suddenly takes 10x longer after a bulk data load. The statistics haven't been updated since the load, so the optimizer underestimates row counts and chooses a nested loop join instead of a hash join.
Statistics Management
Statistics help the query optimizer estimate result sizes and choose efficient execution plans:
| Action | Command | When to Use |
|---|---|---|
| Update all statistics | UPDATE STATISTICS dbo.TableName | After bulk loads |
| Create specific stats | CREATE STATISTICS stat_name ON dbo.Table(Column) | Frequently filtered columns |
| Auto-create statistics | Enabled by default | Good for most scenarios |
| View statistics health | DBCC SHOW_STATISTICS('dbo.Table', 'stat_name') | Diagnosing slow queries |
When statistics go stale:
- After loading >20% new data into a table
- After significant DELETE operations
- After schema changes (new columns)
Automatic vs. manual: At query time the engine auto-creates missing single-column statistics and refreshes stale ones. That work is synchronous, so the triggering query pays for it; proactive statistics refresh (on by default) tries to do those refreshes ahead of time after data changes, but only for auto-created statistics. None of this updates statistics continuously as rows are inserted, so run UPDATE STATISTICS right after large loads. Only single-column statistics can be created manually; multi-column statistics aren't supported.
Why stale statistics hurt so much here: The warehouse engine is distributed, so row-count estimates decide the join strategy and data movement. For example, it may broadcast a small dimension to every node, or it may shuffle (redistribute) both tables for a hash join. A wrong estimate on a 3,650-row date dimension can add a large shuffle that disappears once statistics are current.
Result Set Caching
Fabric warehouses automatically cache query results:
- How it works: Identical queries return cached results without re-executing
- Cache duration: Invalidated when any referenced table changes; unused cache is evicted automatically (after 24 hours without a hit)
- Benefit: Repeated dashboard queries return instantly
- Limitation: Only benefits identical queries—parameter changes miss the cache. Only pure
SELECTqueries qualify: not CTAS or DML, and not queries usingGETDATE(), row-level security or dynamic data masking. The query must also read at least one table of 100,000+ rows. - Configuration: On by default; turn off with
ALTER DATABASE <name> SET RESULT_SET_CACHING OFF, or per query withOPTION (USE HINT ('DISABLE_RESULT_SET_CACHE'))
Query Performance Best Practices
| Practice | Why | Example |
|---|---|---|
| Filter early (WHERE clause) | Reduce data scanned | WHERE date > '2024-01-01' before joining |
| **Avoid SELECT *** | Reads unnecessary columns | Specify only needed columns |
| Use appropriate JOINs | Wrong join = wrong results or poor performance | INNER vs LEFT based on requirements |
| Minimize DISTINCT | Forces expensive sorting | Redesign query or fix upstream duplicates |
| Materialize a CTE used several times | The optimizer inlines a CTE, so every reference re-runs its query | Load a CTE referenced 2+ times (e.g. a large aggregation) into a #temp table once, then join to it |
| Use CTEs for readability | Optimizer flattens them anyway | No performance cost, easier to debug |
No index or partition tuning in the warehouse: Fabric Warehouse tables have no user-defined indexes to add, drop or rebuild. Partitioned tables and computed columns aren't supported either. Your levers are statistics, query shape, caching and, for large tables filtered on mid-to-high-cardinality columns (IDs, dates), data clustering (preview), set with WITH (CLUSTER BY (col)) when the table is created. Clustering helps little on a low-cardinality column (a handful of values, such as a tier or region). If a workload needs partition pruning on such a column, store the table as a lakehouse Delta table partitioned on it and query it through the SQL analytics endpoint.
Workload isolation (custom SQL pools, preview): By default the warehouse isolates SELECT work from ingestion (non-SELECT) work. To stop ad-hoc queries delaying critical reports, a workspace admin creates custom SQL pools (up to 8 per workspace). The admin classifies requests into them by the connection's application name and caps each pool's share of resources. Query timeouts only cancel work, and statistics don't prioritize it.
Materialized Views (Preview)
- Status: Not supported in Fabric Data Warehouse. Materialized views are on the unsupported T-SQL list; they are a Synapse dedicated SQL pool feature.
- Warehouse alternative: A pre-aggregated summary table rebuilt on a schedule with CTAS or
INSERT...SELECT, plus result set caching for repeated identical queries - Where materialized views do exist in Fabric: KQL materialized views in an Eventhouse (see 4.3.5) and materialized lake views in a lakehouse
⚠️ Exam Trap: Result set caching is on by default—you don't need to enable it or add a query hint. When a question describes a slow first run and fast identical re-runs, or asks what makes repeated identical queries fast, the answer is result set caching. When a query is slow on every run, look at statistics and query shape instead.
⚠️ Common Pitfall: Running UPDATE STATISTICS on every table daily regardless of data changes. Only update statistics on tables that received significant data modifications.
