Synapse Performance Optimisation

The performance optimisation hierarchy

Work through these areas in order. Each level builds on the one before, and the earlier items typically have more impact than later ones:

1. Distribution strategy — eliminating cross-node data movement is the single biggest performance lever
2. Statistics — without up-to-date statistics, the query optimiser makes poor execution plan choices
3. Indexes — clustered columnstore indexes for large tables; heap for small staging tables
4. Partitioning — enables partition elimination for date-filtered queries
5. Result-set caching — makes identical repeated queries free
6. Materialised views — pre-compute expensive aggregations so dashboards never run them live
7. Workload management — ensure high-priority queries get resources, low-priority queries do not starve the pool

Statistics: the most overlooked optimisation

The Synapse query optimiser uses statistics to estimate row counts, data distribution, and cardinality when building execution plans. Without accurate statistics, the optimiser may choose a nested loop join when a hash join would be 100x faster, or broadcast a 10 GB table to all nodes when a local join would do.

Synapse does not automatically create statistics on all columns by default. You must create them, and you must update them after large data loads.

-- Create statistics on columns used in WHERE clauses, JOIN conditions, and GROUP BY
CREATE STATISTICS stats_FactOrders_customer_id
    ON dbo.FactOrders (customer_id);

CREATE STATISTICS stats_FactOrders_order_date
    ON dbo.FactOrders (order_date);

CREATE STATISTICS stats_FactOrders_product_id
    ON dbo.FactOrders (product_id);

-- Create multi-column statistics for common join patterns
CREATE STATISTICS stats_FactOrders_date_region
    ON dbo.FactOrders (order_date, region);

-- Update statistics after a large data load
UPDATE STATISTICS dbo.FactOrders;

-- Update statistics on a specific column only (faster for targeted updates)
UPDATE STATISTICS dbo.FactOrders (stats_FactOrders_order_date);

-- Check when statistics were last updated
SELECT
    s.name AS stat_name,
    c.name AS column_name,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM
    sys.stats s
    JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
    JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE
    OBJECT_NAME(s.object_id) = 'FactOrders'
ORDER BY
    last_updated DESC;

Index choices: CCI, row store, and heap

Synapse supports three index types, each suited to different table sizes and query patterns:

The Clustered Columnstore Index (CCI) is almost always the right choice for fact tables. It stores data column by column rather than row by row, which means queries that touch only a few columns read a fraction of the total data. It also achieves 5-10x compression over row-store, reducing both storage cost and I/O time.

-- Rebuild a CCI to fix fragmented rowgroups after many small inserts
-- Fragmented rowgroups (OPEN or COMPRESSED with < 100k rows) hurt performance
ALTER INDEX ALL ON dbo.FactOrders REBUILD;

-- Check rowgroup health on a CCI
SELECT
    rg.state_desc,
    rg.total_rows,
    rg.deleted_rows,
    rg.size_in_bytes / 1024.0 / 1024.0 AS size_mb,
    COUNT(*) AS rowgroup_count
FROM
    sys.pdw_nodes_column_store_row_groups rg
    JOIN sys.pdw_nodes_tables t ON rg.object_id = t.object_id
    JOIN sys.tables tab ON t.name = tab.name
WHERE
    tab.name = 'FactOrders'
GROUP BY
    rg.state_desc, rg.total_rows, rg.deleted_rows, rg.size_in_bytes
ORDER BY
    rowgroup_count DESC;

Result-set caching

Result-set caching stores query results on the control node. When an identical query is submitted (same SQL text, same user session parameters), Synapse returns the cached result without executing the query at all. This is transformative for Power BI dashboards where the same report queries are fired repeatedly by multiple users.

-- Enable result-set caching at the database level
ALTER DATABASE myDedicatedPool
SET RESULT_SET_CACHING ON;

-- Check if your last query was served from cache
-- Look for 'HIT' in the result_cache_hit column
SELECT TOP 10
    request_id,
    command,
    result_cache_hit,
    total_elapsed_time
FROM
    sys.dm_pdw_exec_requests
WHERE
    status = 'Completed'
ORDER BY
    end_compile_time DESC;

-- Disable caching for a specific query (useful for testing performance)
SELECT /*+ NO_RESULT_SET_CACHE */
    COUNT(*) FROM dbo.FactOrders;

Result-set caching is invalidated when the underlying tables are modified. If you load new data into FactOrders, the cached results for queries against it are immediately invalidated. The cache is also bounded in size — large result sets may not be cached, and the oldest cached results are evicted when the cache fills.

Materialised views for expensive aggregations

Materialised views pre-compute and persist the results of complex queries. Unlike result-set caching, materialised views are transparent to the query optimiser — it can use them even if your query does not reference them explicitly. This is powerful: you can add a materialised view for a slow aggregation without changing any existing reports or BI queries.

-- Create a materialised view that pre-aggregates monthly revenue by region
CREATE MATERIALIZED VIEW dbo.mv_monthly_revenue_by_region
WITH (DISTRIBUTION = HASH(region))
AS
SELECT
    region,
    YEAR(order_date)    AS order_year,
    MONTH(order_date)   AS order_month,
    COUNT(order_id)     AS order_count,
    SUM(order_total)    AS total_revenue,
    AVG(order_total)    AS avg_order_value
FROM
    dbo.FactOrders
GROUP BY
    region,
    YEAR(order_date),
    MONTH(order_date);

-- Now this slow query against the base table:
SELECT region, YEAR(order_date), MONTH(order_date), SUM(order_total)
FROM dbo.FactOrders
GROUP BY region, YEAR(order_date), MONTH(order_date);

-- ...may automatically use the materialised view (check EXPLAIN plan)
EXPLAIN
SELECT region, YEAR(order_date), MONTH(order_date), SUM(order_total)
FROM dbo.FactOrders
GROUP BY region, YEAR(order_date), MONTH(order_date);

Materialised views add storage overhead and increase write latency slightly (since they must be updated when base tables change). Only create them for queries that are run frequently and are expensive to compute.

Workload management: preventing one query from starving others

By default, all queries compete equally for DWU resources. In production, you want important queries (like end-of-day financial reports) to get priority over exploratory ad-hoc queries from analysts. Synapse’s workload management system lets you define workload groups and classifiers.

-- Create a workload group for high-priority reports
-- This group gets guaranteed 40% of resources, can burst to 100%
CREATE WORKLOAD GROUP HighPriorityReports
WITH
(
    MIN_PERCENTAGE_RESOURCE = 40,
    CAP_PERCENTAGE_RESOURCE = 100,
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 5
);

-- Create a workload group for low-priority exploration
CREATE WORKLOAD GROUP LowPriorityExploration
WITH
(
    MIN_PERCENTAGE_RESOURCE = 0,
    CAP_PERCENTAGE_RESOURCE = 20,
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 1
);

-- Create classifiers to route specific users or labels to workload groups
CREATE WORKLOAD CLASSIFIER high_priority_classifier
WITH
(
    WORKLOAD_GROUP = 'HighPriorityReports',
    MEMBERNAME = 'svc_powerbi_reports',
    PRIORITY = HIGH
);

CREATE WORKLOAD CLASSIFIER low_priority_classifier
WITH
(
    WORKLOAD_GROUP = 'LowPriorityExploration',
    MEMBERNAME = 'analysts_group',
    PRIORITY = LOW
);

Diagnosing slow queries with DMVs

When a query is slow, use the Dynamic Management Views (DMVs) to understand why:

-- Find the slowest running queries in the last hour
SELECT TOP 20
    r.request_id,
    r.status,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    r.resource_class,
    r.command
FROM
    sys.dm_pdw_exec_requests r
WHERE
    r.status NOT IN ('Completed', 'Failed', 'Cancelled')
    OR r.end_time > DATEADD(HOUR, -1, GETDATE())
ORDER BY
    r.total_elapsed_time DESC;

-- Find queries waiting for resources (queued)
SELECT
    w.queued_time_ms / 1000   AS queued_seconds,
    w.request_id,
    r.command
FROM
    sys.dm_pdw_waits w
    JOIN sys.dm_pdw_exec_requests r ON w.request_id = r.request_id
WHERE
    w.state = 'Queued'
ORDER BY
    w.queued_time_ms DESC;