Data Warehouse Performance Engineering on SQL Server

Start with the execution plan

Tuning without reading the plan is guessing. Look for the expensive operators: large scans where a seek was expected, key lookups repeated millions of times, spills to tempdb, and estimates that differ wildly from actual row counts — usually a sign of stale statistics or non-sargable predicates.

Columnstore for facts

Clustered columnstore indexes compress fact tables dramatically and enable batch-mode execution. On one warehouse, a 212 GB rowstore fact became 31 GB, and the main sales report went from 48 seconds to just over 3.

• Load in batches of at least ~100K rows to create compressed rowgroups
• Rebuild or reorganise to remove deleted rows and open delta stores
• Keep a rowstore nonclustered index only where point lookups need it

Load without blocking readers

Partition switching lets you load a staging table with the same structure, build its indexes, then switch it into the fact table as a metadata-only operation. Reports keep reading the old data until the switch completes in milliseconds.