Optimizing Query Performance in Azure Synapse Dedicated SQL Pools

Chanaka Prabath

7 min read

·

Sep 2, 2026

--

Listen

Share

Why Data Layout Matters More Than Query Tuning

When a query against a dedicated SQL pool in Azure Synapse runs slower than expected, the instinct is often to rewrite the query — add hints, restructure joins, tweak the WHERE clause. But in Synapse, the biggest performance wins usually come from how the table itself is laid out long before any query touches it. Three decisions drive that layout: distribution, indexing, and partitioning. Get these right, and even a simple query flies. Get them wrong, and no amount of query rewriting will fully compensate.

This article walks through each of these three layers, how they interact, and the mistakes that most commonly hurt performance in production.

Press enter or click to view image in full size

Layer 1: Distribution — Spreading Data Across Distributions

A dedicated SQL pool splits every table across 60 distributions under the hood. How a table’s rows get assigned to those distributions is controlled by the DISTRIBUTION setting, and it's the single most consequential decision you'll make for a large table.

Hash Distribution

CREATE TABLE dbo.Sales
(
    SalesOrderID INT,
    CustomerID   INT,
    ProductID    INT,
    Amount       DECIMAL(10,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX
)

With hash distribution, Synapse hashes the values in the chosen column to decide which distribution each row lands on. The goal is twofold:

• Even spread. Choose a high cardinality column (many unique values) so rows are distributed roughly evenly across all 60 distributions. A low cardinality column — a status flag, a boolean, a small lookup code — will cluster rows onto just a few distributions, creating “hot” nodes that do most of the work while others sit idle.
• Minimizing data movement. Choose a column that’s heavily used in joins and aggregations. If two large tables are both hash distributed on the same join key, Synapse can perform the join without shuffling data between nodes — a huge performance difference compared to a join that forces a full data movement operation across the cluster.

There’s no universal “correct” column to hash on — it depends entirely on the query patterns your workload actually runs. A customer centric workload benefits from hashing on CustomerID; a product centric workload might do better hashing on ProductID. Before deciding, it's worth checking for skew:

SELECT CustomerID, COUNT(*) AS row_count
FROM dbo.Sales_Staging
GROUP BY CustomerID
ORDER BY row_count DESC

If a handful of values dominate the row count, that column is a poor distribution key candidate no matter how central it is to your queries.

Other Distribution Options

• Round robin spreads rows evenly with no logic tied to column values — a safe default for staging tables or when no clear join/aggregation pattern exists yet, at the cost of extra data movement during joins.
• Replicate copies the entire table to every distribution — ideal for small dimension tables (a few thousand rows) that get joined against large fact tables, since it avoids data movement entirely for those joins.

Indexing — How Data Is Physically Stored

Distribution decides which distribution a row belongs to, and those distributions are mapped across the compute nodes. Indexing decides how that row is stored once it’s there. In Synapse, a table can have exactly one clustered structure — you have to pick one:

Press enter or click to view image in full size

Clustered columnstore is the default and the right choice for most large fact tables. Because each column is stored separately and heavily compressed, a query that touches only 3 of 50 columns reads only those 3 columns’ data — a dramatic I/O reduction for analytical, aggregation heavy queries.

Clustered index (rowstore) still has its place: smaller dimension tables, or tables with heavy single row lookup and update patterns, benefit from a traditional sorted, row oriented structure.

These two are mutually exclusive — a table is either columnstore or rowstore at its core. For rowstore tables using a clustered index, a nonclustered index can be added when a specific column is frequently used for selective filtering or lookups. However, a rowstore nonclustered index cannot be added to a table that already has a clustered columnstore index in a dedicated SQL pool:

CREATE NONCLUSTERED INDEX IX_Sales_ProductID
ON dbo.Sales (ProductID)

Nonclustered indexes aren’t free, though — they add storage and maintenance overhead on every insert, update, and delete. Add one only when you have a genuine, frequent, highly selective query pattern that the columnstore index alone doesn’t serve well; joins and GROUP BY operations are usually handled efficiently by columnstore without any extra index.

Partitioning — Slicing Data Within Each Distribution

Partitioning adds a third, independent layer on top of distribution and indexing:

CREATE TABLE dbo.Sales
(
    SalesOrderID INT,
    CustomerID   INT,
    OrderDate    DATE,
    Amount       DECIMAL(10,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION
    (
        OrderDate RANGE RIGHT FOR VALUES
        ('2024-01-01', '2025-01-01', '2026-01-01')
    )
)

The three layers stack like this:

Table
 └── Distributed across 60 distributions (by hash key)
      └── Each distribution split into partitions (typically by date)
           └── Each partition stored as clustered columnstore

Partitioning is most valuable for two things: fast bulk operations — dropping or truncating a single month’s partition instead of running a slow, row by row DELETE — and query pruning, where a filter like WHERE OrderDate >= '2026-01-01' lets Synapse skip scanning older partitions entirely.

The Over Partitioning Trap

This is one of the most common mistakes in Synapse table design. Data is already split across 60 distributions. Adding too many partitions on top of that splits the data further — sometimes into rowgroups that are much smaller than the roughly 1 million rows that can fit in an ideal columnstore rowgroup, which can reduce compression efficiency and query performance.

Daily partitioning on a table that isn’t large enough to support that level of granularity is a common culprit. Partitioning is generally most useful for large tables where partition elimination or partition level data maintenance provides a measurable benefit. Favor a partition granularity that matches the workload — often monthly or yearly rather than daily — and consider whether the average number of rows per distribution per partition is large enough to produce healthy columnstore rowgroups.

Bringing It Together: A Real World Scenario

Consider an e commerce transactions table distributed by customer_id and partitioned by purchase_date:

CREATE TABLE dbo.Transactions
(
    TransactionID BIGINT,
    CustomerID    INT,
    PurchaseDate  DATE,
    Amount        DECIMAL(10,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION
    (
        PurchaseDate RANGE RIGHT FOR VALUES
        ('2024-01-01', '2025-01-01', '2026-01-01')
    )
)

Now consider a query that frequently filters by both customer and date:

SELECT
    TransactionID,
    CustomerID,
    PurchaseDate,
    Amount
FROM dbo.Transactions
WHERE CustomerID = 12345
  AND PurchaseDate >= '2026-01-01';

This table design gives the optimizer two important opportunities.

First, partition elimination can remove partitions outside the requested PurchaseDate range. Instead of scanning the entire table, Synapse only needs to consider the partitions containing data from 2026 onward.

Second, because the table is hash distributed on CustomerID, rows for the same customer are directed to the same distribution. This can reduce data movement for queries and joins involving CustomerID, provided the distribution key produces a reasonably balanced distribution.

The important point is that partitioning and distribution solve different problems:

• Partitioning helps eliminate large sections of data based on the partitioning column.
• Distribution determines where the data is physically located across the 60 distributions and can reduce data movement when the distribution key matches common join patterns.
• Columnstore storage reduces the amount of data that must be read by storing columns separately and using compression.

If the query is still slow, the answer should not automatically be to add another index. In a dedicated SQL pool, first investigate the fundamentals: data distribution and skew, partition sizing, columnstore rowgroup quality, statistics, and data movement in the execution plan.

For example, you can inspect the distribution of CustomerID to identify potential skew:

SELECT
    CustomerID,
    COUNT(*) AS row_count
FROM dbo.Transactions
GROUP BY CustomerID
ORDER BY row_count DESC;

A customer with an exceptionally large number of transactions can create an uneven workload even though CustomerID has high cardinality overall. Distribution key selection therefore needs to consider both cardinality and frequency distribution, not simply the number of distinct values.

The broader lesson is that there is no single optimization technique that fixes every query. A good Synapse design aligns the distribution key, partitioning strategy, columnstore structure, statistics, and workload with the queries that actually run against the table.

Summary Checklist

• Distribution: Hash on a high cardinality column central to your joins/aggregations. Check for skew before committing. Use round robin for staging tables, replicate for small dimensions.
• Indexing: Default to clustered columnstore for large analytical fact tables. Use clustered rowstore indexes where the workload favors point lookups or frequent updates. Choose the physical storage structure based on table size and query pattern.
• Partitioning: Use it for large tables needing fast bulk load/delete and date range query pruning. Don’t over partition — keep row groups large enough for columnstore compression to do its job.

None of these three decisions exists in isolation. The right distribution key depends on your join patterns; the right index depends on table size and query shape; the right partitioning strategy depends on how the first two are already splitting your data. Getting all three aligned with your actual workload — not just what feels intuitively “correct” — is what separates a dedicated SQL pool that scales gracefully from one that grinds to a halt under load.