SQL serving cheat sheet
To deliver fast, reliable analytics from the Lakehouse platform, it's essential to configure and operate SQL warehouses for optimal BI performance. SQL warehouses in Databricks are purpose-built for serving business intelligence workloads, enabling dynamic scaling, efficient query processing, and robust resource management.
This page outlines recommended practices for provisioning, managing, and monitoring SQL warehouses to ensure responsive dashboards, cost-effective resource usage, and smooth integration with enterprise BI tools.
This content is intended for data engineers, BI developers, and workspace administrators responsible for configuring, optimizing, and maintaining SQL warehouses for analytics and dashboard performance. Many tasks require advanced workspace permissions that allow you to create or manage SQL warehouses.
SQL serving
Best practice | Impact | Docs | Action items |
|---|---|---|---|
Use serverless compute to automatically start, stop, and scale resources | Reduces costs by stopping idle resources. | Enable Auto stop for development warehouses | |
Use SQL warehouses for any BI workload (serverless is recommended) | SQL warehouses are optimized for BI workloads. | Configure SQL warehouse for BI workloads | |
Right-size your warehouse | Balances performance and cost for your workload. | Start with M size, monitor performance, and adjust if needed | |
Use a higher cluster size for larger datasets | The larger the cluster (M, L, XL, etc.), the faster complex queries run. If you have only simple, short-running queries, don't increase the size (might be slower due to data shuffling). | Evaluate query complexity and dataset size | |
Use SQL warehouse scaling | A SQL warehouse scales out to handle increased workload. When the warehouse hits its limits, queries get queued, not rejected. | Enable scaling for production workloads | |
If expecting many concurrent queries, increase the minimum number of clusters | Prevents queries from being queued while waiting for scale-out. | Configure min clusters based on expected workload | |
Use separate SQL warehouses for different workloads or business units | Right-sizes SQL warehouses to improve isolation and cost attribution. | Create dedicated warehouses per workload | |
Monitor query performance | Identifies performance bottlenecks and issues using query history. System tables allow you to programmatically monitor performance. | Set up monitoring dashboards |
Related content
For detailed guidance on analyzing BI workload requirements and configuring SQL warehouses for different access patterns (DirectQuery vs Import/Extract), see SQL warehouse settings for BI workloads.