Consultants and Trainers

Azure Dedicated SQL Pool vs. Serverless SQL Pool

Azure Synapse Analytics is a data analytics service that includes all services that are required for ingestion, transformation, and visualization of data. It includes Spark Pool and SQL Pools for data analytics. There two types of SQL Pools – dedicated and serverless.

Dedicated SQL Pools

• Used for analyzing data loaded into Synapse internal tables.

• User managed and can be any number per account subject to enterprise wise limitations.

• Uses fixed resources, not scalable.

• Contains all features of Enterprise SQL ata Warehouse

• Its instance must be created and resources must be provisioned

• Resources are measured in Data Warehousing Units – (DWUs).

• A WDU is combination of allocated CPU, Memory and IO resources.

• Number of DWUs determines query performance and cost.

• Users are charged for how long the Dedicated Pool is active and not on number of queries executed.

• Dedicated pools can be paused and resumed. 

• Data can be loaded into Pool using COPY command.

• Node based and query execution is distributed across nodes

• Advantage with SQL pool storage includes        

• No external table to define

• Storage of data in relational format, so better performance and less storage

• Storage is charged separately, independent from compute power

• Stores a table as Clustered Column store index by default

• This storage format achieves high compression and better query performance on large table

• Partitioning of data is supported

Serverless Pools

• Synapse Workspace has a built-in serverless SQL Pool

• It acts as query service over data in Azure Data Lake through an ‘external table’.

• Does not require any extra configuration, it is used as Software as a Service (SaaS).

• User does not have to setup infrastructure; it is provided by Azure.

• The OPENROWSET function of T-SQL is used for querying data.

• User pays for the amount of processed data.

• It creates statistics that are reused for multiple execution of the same query or queries with similar query execution plan.

• Enables querying of large volume of data directly from Data Lake.

• Uses external table for SQL query execution 

• Azure allows only one serverless pool per account

• Azure automatically scales a the serverless pool depending on processing requirement.

• There are no fixed resources linked to it.

• It is a default option in Synapse workspace for SQL pools.  

• It is a node based and query processing is distributed across nodes.

• It can process parquet, ORC, Delimited text formats of data.