This chapter covers Azure Synapse Analytics Dedicated SQL Pools and Serverless SQL Pools, two distinct query execution engines within the same Synapse workspace. For DP-900, this topic appears in domain 3 (Analytics) under objective 3.1, which focuses on identifying appropriate analytics components. Approximately 10-15% of exam questions touch on the differences between dedicated and serverless SQL pools, including use cases, pricing models, and performance characteristics. Understanding when to use each is critical for scenario-based questions.
Jump to a section
Imagine a large cake factory (Dedicated SQL Pool) that has its own ovens, mixers, and staff. The factory is always running, even when no cakes are being made, because it takes significant time to preheat the ovens and prepare the production line. You pay for the factory whether you use it or not. If you need to produce 100 cakes every hour, this factory is perfect because it provides consistent performance and you can schedule production runs. However, if you only need a few cakes occasionally, you are wasting money on idle capacity.
Now consider an on-demand bakery (Serverless SQL Pool). This bakery has no ovens or staff of its own. Instead, when an order comes in, it rents time at a shared commercial kitchen. You only pay for the actual time the ovens are running and the ingredients used. There is no setup time — the kitchen is already preheated and ready. If you need one cake, you get it quickly and cheaply. But if you need 100 cakes per hour, the shared kitchen may not have enough capacity, and the cost per cake might be higher due to rental fees. The on-demand bakery is ideal for ad-hoc requests, experimentation, or unpredictable workloads, while the factory is better for steady, high-volume production.
What Are Synapse SQL Pools?
Azure Synapse Analytics is a unified analytics platform that combines big data and data warehousing. It provides two SQL-based query engines: Dedicated SQL Pool (formerly SQL Data Warehouse) and Serverless SQL Pool (formerly SQL on-demand). Both allow you to query data using T-SQL, but they differ fundamentally in architecture, provisioning, performance, and cost.
Dedicated SQL Pool: The Provisioned Engine
A Dedicated SQL Pool is a provisioned, always-on cluster of compute nodes that stores data in relational tables. It is designed for large-scale, predictable workloads where performance consistency is critical. The pool consists of a control node and multiple compute nodes. The control node manages query distribution, while compute nodes execute queries in parallel (MPP architecture). You provision a specific Data Warehouse Units (DWU) size, which determines the number of compute nodes and their resources. DWU ranges from DW100c to DW30000c. The pool must be paused when not in use to avoid charges; otherwise, you pay per hour for the provisioned DWU.
Serverless SQL Pool: The On-Demand Engine
A Serverless SQL Pool is a pay-per-query service that does not require provisioning. It spins up compute resources on demand to execute queries against data stored in Azure Storage (Blob or ADLS Gen2) or directly in the lake. There is no dedicated storage — you query external data using OPENROWSET or external tables. The compute resources are automatically scaled based on the query complexity and data size. You pay only for the amount of data processed (in TB) for each query. Serverless SQL Pool is ideal for ad-hoc analysis, data exploration, and logical data warehousing without moving data.
Architecture Comparison
Dedicated SQL Pool Architecture: - Control node: Receives T-SQL queries, optimizes and parallelizes them, then distributes work to compute nodes. - Compute nodes: Store data in distributions (60 distributions per database). Each compute node hosts multiple distributions. Data is hash-distributed or round-robin distributed across distributions. - Storage: Data is stored in Azure Premium Blob Storage, decoupled from compute. Compute nodes cache data in local SSDs for performance. - Scaling: You can scale up (increase DWU) or scale down (decrease DWU) manually or via autoscale. Scaling takes 1-5 minutes. - Pausing: When paused, compute resources are deallocated; you pay only for storage.
Serverless SQL Pool Architecture: - No control node or compute nodes provisioned. Instead, a shared pool of compute resources is dynamically allocated. - Query execution: When a query is submitted, Synapse allocates resources from a shared pool, reads data directly from storage, and returns results. The compute resources are released after the query completes. - Storage: Data remains in Azure Storage (Blob or ADLS Gen2). No data is moved or ingested into the pool. - Concurrency: There is a limit of 20 concurrent queries per workspace by default (adjustable). - Resource limits: Maximum query duration is 30 minutes. Maximum result set size is 200 GB per query.
Performance and Scaling
Dedicated SQL Pool Performance: - Consistent performance: Because resources are dedicated, query execution times are predictable. - MPP parallelism: Queries are distributed across all compute nodes, enabling fast processing of large datasets. - Materialized views, indexes, and partitioning: You can create indexes (clustered columnstore, clustered, nonclustered) and partition tables to improve query performance. - Result set caching: Enabled by default for queries that read less than 100 GB. Cached results are returned instantly for repeated queries.
Serverless SQL Pool Performance: - Variable performance: Execution time depends on the current load on the shared compute pool and the amount of data scanned. - No indexes or materialized views: Since data is external, you cannot create indexes. Performance relies on file format (Parquet is faster than CSV), file partitioning (partition elimination), and predicate pushdown. - Automatic query optimization: The engine uses statistics and file metadata to optimize query plans. - Result set caching: Not available. Each query re-scans the data.
Cost Model
Dedicated SQL Pool Cost: - Compute cost: Per hour based on DWU size. Example: DW100c costs approx. $1.00/hour (varies by region). - Storage cost: Separate, based on data stored in Azure Premium Blob Storage (~$0.05/GB/month). - Paused state: No compute cost, only storage cost. - Minimum commitment: You must provision a pool; there is no pay-per-query option.
Serverless SQL Pool Cost: - Compute cost: Per TB of data processed. Example: $5.00 per TB processed (varies by region). - No storage cost: You pay only for the storage where your data resides (Azure Storage costs). - No minimum commitment: You pay only for queries executed. - Cost optimization: Use file formats like Parquet with compression, and filter data early using WHERE clauses to reduce data scanned.
Use Cases
Dedicated SQL Pool Use Cases: - Enterprise data warehousing with predictable, high-volume ETL/ELT workloads. - Scenarios requiring consistent sub-second query performance. - When you need to store relational data in the warehouse (not just query external data). - When you need advanced features like materialized views, indexes, or table partitioning.
Serverless SQL Pool Use Cases: - Ad-hoc data exploration and discovery on data lakes. - Logical data warehousing where you query data in place without moving it. - Small to medium-sized analytics workloads where cost is a concern. - Prototyping and development before moving to a dedicated pool.
How to Choose
Determine if you need to store data in the warehouse or query external data in place.
Evaluate workload predictability: steady, high-volume → Dedicated; sporadic, variable → Serverless.
Consider performance requirements: consistent low latency → Dedicated; acceptable variable latency → Serverless.
Analyze cost: If data volumes are large and queries are frequent, Dedicated may be cheaper. If queries are occasional, Serverless is more cost-effective.
Check feature requirements: Indexes, materialized views, partitioning → Dedicated. Only basic T-SQL and external data access → Serverless.
Integration with Other Services
Both SQL pools integrate with Azure Data Factory, Azure Synapse Pipelines, Power BI, and other Azure services. However, Dedicated SQL Pool supports more advanced integration like PolyBase for loading data, while Serverless SQL Pool can directly query Cosmos DB (analytical store) and Azure Databricks tables.
Exam Tips
Remember that Dedicated SQL Pool is provisioned (always on) and you pay per hour. Serverless SQL Pool is on-demand and you pay per TB processed.
Know that Serverless SQL Pool cannot store tables — it only queries external data.
Understand that Dedicated SQL Pool uses MPP and supports indexes; Serverless does not.
Be aware that pausing is only applicable to Dedicated SQL Pool.
For scenario questions: If the question mentions "ad-hoc analysis" or "data lake", think Serverless. If it mentions "consistent performance" or "data warehousing", think Dedicated.
Provision a Dedicated SQL Pool
In the Azure portal, navigate to the Synapse workspace and select 'New SQL Pool'. Choose a name and specify the Data Warehouse Units (DWU) size, e.g., DW100c. The pool will be provisioned with a control node and compute nodes. Provisioning takes 1-5 minutes. Once active, you can connect using SQL Server Management Studio (SSMS) or Azure Data Studio. The pool is billed per hour whether or not queries are running.
Create Tables in Dedicated Pool
After provisioning, create tables using CREATE TABLE statements. You must specify distribution style (HASH, ROUND_ROBIN, REPLICATE) and index type (CLUSTERED COLUMNSTORE, CLUSTERED, NONCLUSTERED). For example, CREATE TABLE Sales (ID INT, Amount DECIMAL) WITH (DISTRIBUTION = HASH(ID), CLUSTERED COLUMNSTORE INDEX). Data is distributed across 60 distributions. Loading data can be done via PolyBase, COPY command, or Azure Data Factory.
Query Data in Dedicated Pool
Submit a T-SQL query. The control node parses and optimizes the query, then distributes execution plans to compute nodes. Each compute node processes its local data in parallel. Results are aggregated at the control node and returned. For repeated queries, result set caching may return cached results. The query completes within predictable time due to dedicated resources.
Use Serverless SQL Pool for Ad-Hoc Query
In the Synapse workspace, select 'Serverless SQL Pool' (already exists as 'Built-in'). Use OPENROWSET to query files in Azure Storage. Example: SELECT * FROM OPENROWSET(BULK 'https://storageaccount.blob.core.windows.net/container/*.parquet', FORMAT='PARQUET') AS [result]. The engine allocates compute resources from a shared pool, reads the files directly, and returns results. You pay only for the data scanned. No setup or provisioning needed.
Create External Tables in Serverless Pool
To simplify queries, create external tables that map to file locations. First, create a database and a data source referencing the storage account. Then create an external file format (e.g., Parquet) and an external table. Example: CREATE EXTERNAL TABLE Sales (ID INT, Amount DECIMAL) WITH (LOCATION='sales/', DATA_SOURCE=MyDataSource, FILE_FORMAT=ParquetFormat). Queries against external tables are similar to regular tables but data remains in storage.
Monitor and Scale Dedicated Pool
Monitor DWU usage and query performance in Azure portal. If performance is insufficient, scale up the pool by adjusting DWU. Scaling can be done manually or via autoscale rules. For example, scale from DW100c to DW200c. Scaling takes 1-5 minutes and briefly interrupts running queries. Alternatively, pause the pool to stop compute billing. Resume later to continue.
Monitor Cost in Serverless Pool
Monitor data processed per query using sys.dm_exec_requests or Azure portal. The cost is based on the amount of data scanned (in TB). To reduce cost, ensure queries use columnar formats like Parquet and include filters that enable partition elimination. Use the 'cost threshold for parallelism' setting to limit resource usage. There is no scaling or pausing because compute is automatically managed.
Enterprise Scenario 1: Large Retail Data Warehouse
A multinational retailer needs to store and query years of sales transactions totaling 50 TB. They require consistent sub-second response times for dashboards and reports. They choose a Dedicated SQL Pool with DW1000c (approximately 1000 compute nodes). They create hash-distributed tables on CustomerID to optimize joins. ETL processes run nightly using Azure Data Factory to load data from on-premises SQL Server into the pool. The pool is scaled to DW2000c during ETL and scaled back to DW1000c for reporting. They pause the pool on weekends to save costs. Without careful scaling, they would overpay for idle resources or underperform during peak loads.
Enterprise Scenario 2: Data Lake Analytics for Marketing
A marketing team has raw event data in Parquet format in Azure Data Lake Storage Gen2. They need to run ad-hoc queries to analyze campaign performance, often with unpredictable query patterns. They use Serverless SQL Pool to query the data directly. They create external tables partitioned by date to enable partition elimination. Queries that filter on a specific month scan only that month's data, reducing cost. The team runs exploratory queries during business hours and pays only for data scanned. If they mistakenly used CSV files instead of Parquet, costs would be 5-10x higher due to more data scanned. Also, without proper partitioning, queries would scan the entire dataset, leading to high costs and slow performance.
Common Misconfiguration Issues
Dedicated Pool Not Paused: Forgetting to pause a Dedicated SQL Pool when not in use results in continuous compute charges. A pool left running over a weekend can incur significant cost.
Serverless Pool Scanning Too Much Data: Writing queries without filters or using inefficient file formats (CSV) causes excessive data scanning and high costs. Always use Parquet and include WHERE clauses on partitioned columns.
Incorrect Distribution Choice: In Dedicated Pool, choosing ROUND_ROBIN distribution for a large fact table leads to data movement during joins, degrading performance. HASH distribution on the join key is preferred.
Concurrency Limits: Serverless Pool has a default concurrency limit of 20 queries. If multiple users submit queries simultaneously, some may be queued or fail. This limit can be increased by requesting a support ticket.
DP-900 Exam Focus: Synapse SQL Pools
This topic is tested under Objective 3.1: 'Identify appropriate analytics components for a given scenario.' The exam expects you to distinguish between Dedicated and Serverless SQL Pools based on use cases, pricing, and performance characteristics.
Common Wrong Answers
'Serverless SQL Pool can store tables.' Wrong. Serverless SQL Pool only queries external data; it does not have its own storage. Candidates confuse it with Dedicated SQL Pool which stores relational tables.
'Dedicated SQL Pool is pay-per-query.' Wrong. Dedicated SQL Pool is provisioned and billed per hour. Serverless SQL Pool is pay-per-query (per TB processed).
'Serverless SQL Pool supports indexes.' Wrong. Because data is external, no indexes can be created. Dedicated SQL Pool supports clustered columnstore and other indexes.
'Both pools require provisioning.' Wrong. Only Dedicated SQL Pool requires provisioning. Serverless SQL Pool is always available without provisioning.
Specific Exam Values
DWU range: DW100c to DW30000c.
Serverless SQL Pool cost: ~$5 per TB processed (exact value may vary by region, but the concept is fixed).
Maximum query duration for Serverless: 30 minutes.
Default concurrency limit for Serverless: 20 concurrent queries.
Dedicated SQL Pool can be paused; Serverless cannot.
Edge Cases and Exceptions
If a scenario mentions 'logical data warehouse' or 'data virtualization', the answer is Serverless SQL Pool.
If the scenario requires 'materialized views' or 'indexes', the answer is Dedicated SQL Pool.
For hybrid scenarios: You can use both pools in the same workspace. For example, use Serverless for exploration and Dedicated for production reporting.
The exam may ask about 'Synapse SQL' without specifying dedicated or serverless. In that case, the correct answer depends on the context of the question.
How to Eliminate Wrong Answers
If the question mentions 'provisioning', 'DWU', or 'pause', it's about Dedicated SQL Pool.
If the question mentions 'pay per query', 'data lake', or 'no storage', it's about Serverless SQL Pool.
If the question mentions 'consistent performance', 'indexes', or 'table storage', it's Dedicated.
If the question mentions 'ad-hoc', 'exploration', or 'variable workload', it's Serverless.
Dedicated SQL Pool is provisioned (DWU) and billed per hour; Serverless SQL Pool is pay-per-query (per TB processed).
Serverless SQL Pool cannot store data – it only queries external files in Azure Storage.
Dedicated SQL Pool supports indexes (clustered columnstore), materialized views, and table partitioning.
Serverless SQL Pool has a default concurrency limit of 20 queries and a maximum query duration of 30 minutes.
Pause is only available for Dedicated SQL Pool to stop compute billing.
For predictable, high-performance data warehousing, choose Dedicated SQL Pool.
For ad-hoc data lake exploration and cost-sensitive workloads, choose Serverless SQL Pool.
Both can coexist in the same Synapse workspace for different use cases.
These come up on the exam all the time. Here's how to tell them apart.
Dedicated SQL Pool
Provisioned compute – always on, billed per hour
Stores relational tables with indexes and partitioning
Consistent, predictable performance
Supports materialized views, result set caching
Best for high-volume, steady workloads
Serverless SQL Pool
On-demand compute – pay per TB processed
Queries external data in Azure Storage only
Variable performance based on shared pool load
No indexes, no materialized views, no caching
Best for ad-hoc, sporadic, or exploratory queries
Mistake
Serverless SQL Pool can store tables like a regular database.
Correct
Serverless SQL Pool does not have its own storage. It only queries external data in Azure Storage (Blob or ADLS Gen2) using OPENROWSET or external tables. You cannot CREATE TABLE ... AS SELECT to store data permanently; that would require a Dedicated SQL Pool.
Mistake
Dedicated SQL Pool is pay-per-query.
Correct
Dedicated SQL Pool is provisioned and billed per hour based on the DWU size. You pay even when no queries are running. Only Serverless SQL Pool is pay-per-query (per TB of data processed).
Mistake
Serverless SQL Pool supports indexes for better performance.
Correct
Indexes cannot be created on external data. Serverless SQL Pool relies on file format (Parquet), partitioning, and predicate pushdown for performance. Dedicated SQL Pool supports clustered columnstore, clustered, and nonclustered indexes.
Mistake
Both pools require you to pause when not in use to save costs.
Correct
Only Dedicated SQL Pool can be paused. Serverless SQL Pool is always available and you only pay for queries executed. There is no pause option for Serverless.
Mistake
Serverless SQL Pool can handle the same high-concurrency workloads as Dedicated.
Correct
Serverless SQL Pool has a default concurrency limit of 20 queries. Dedicated SQL Pool can handle many more concurrent queries depending on DWU size. For high concurrency, Dedicated is better.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Yes, a single Synapse workspace can have both a Dedicated SQL Pool (provisioned) and a Serverless SQL Pool (built-in). You can use Serverless for ad-hoc exploration and Dedicated for production workloads. They share the same workspace but are independent engines.
Serverless SQL Pool does not support CTAS because it has no storage. CTAS is only available in Dedicated SQL Pool. In Serverless, you can create external tables as SELECT (CETAS) to write query results back to Azure Storage as files.
Use columnar file formats like Parquet with compression, partition your files by date or region, and include WHERE clauses that filter on partitioned columns. Also avoid SELECT * and only select needed columns. Monitor data scanned using sys.dm_exec_requests.
The query is cancelled and returns an error. You can break the query into smaller chunks or optimize it to scan less data. There is no way to increase the timeout. For long-running queries, consider using Dedicated SQL Pool.
No, Serverless SQL Pool is always on and does not have a pause option. You only pay for queries executed. To avoid costs, simply do not submit queries.
DWU (Data Warehouse Unit) is specific to Azure Synapse Dedicated SQL Pool. DTU (Database Transaction Unit) is used for Azure SQL Database. Both are abstract performance metrics, but DWU is for MPP data warehousing, while DTU is for OLTP databases.
Parquet is best because it is columnar, compressed, and supports predicate pushdown. CSV files require scanning the entire file even if only a few columns are needed, leading to higher costs and slower performance. Always use Parquet when possible.
You've just covered Synapse Dedicated vs Serverless SQL Pools — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?