Home / Data Platform / Warehouses

Data PlatformHow-toAdvanced

Optimization: Synapse dedicated SQL pool - pause/resume, DWU scaling, workload management and caching

Dedicated SQL pools bill per DWU-hour whether busy or idle. Pause and scale on a schedule, isolate loads from reporting with workload groups and classifiers, enable result set caching and materialized views for repeated queries, and keep columnstore and statistics healthy so a smaller DWU does the job.

Deepak Bhardwaj3 min read

Applies toAzure Synapse Analytics dedicated SQL pool (formerly SQL DW); Azure Automation / Logic Apps / pipelines for scheduling

1. Usage pattern

From pool metrics (DWU used %, active queries, queued queries) over 30 days:

Pattern Action
Idle nights/weekends Pause on schedule
Heavy nightly loads, light daytime reporting Scale up for loads, down for daytime
Queued queries at peak with low DWU used % Concurrency/workload management, not more DWU
High DWU used % constantly Optimise queries/data layout before scaling up

2. Pause and scale on a schedule

# Azure Automation runbook (managed identity)
Connect-AzAccount -Identity
Suspend-AzSynapseSqlPool -WorkspaceName syn-prod -Name dwprod            # pause
Resume-AzSynapseSqlPool  -WorkspaceName syn-prod -Name dwprod            # resume
Update-AzSynapseSqlPool  -WorkspaceName syn-prod -Name dwprod -PerformanceLevel DW1000c   # scale
  • Pausing kills running queries and disconnects users. Check for active work first (sys.dm_pdw_exec_requests).
  • Storage keeps billing while paused.
  • Scaling also restarts the pool briefly. Schedule between load and reporting windows.
  • Orchestrate from the pipeline itself: resume → load → scale down → done.

3. Workload management

Isolate loads from BI so neither starves the other:

CREATE WORKLOAD GROUP wg_load WITH (MIN_PERCENTAGE_RESOURCE = 40, CAP_PERCENTAGE_RESOURCE = 70,
  REQUEST_MIN_RESOURCE_GRANT_PERCENT = 10);
CREATE WORKLOAD GROUP wg_bi WITH (MIN_PERCENTAGE_RESOURCE = 30, CAP_PERCENTAGE_RESOURCE = 60,
  REQUEST_MIN_RESOURCE_GRANT_PERCENT = 3);

CREATE WORKLOAD CLASSIFIER wc_load WITH (WORKLOAD_GROUP = 'wg_load', MEMBERNAME = 'etl_loader', IMPORTANCE = HIGH);
CREATE WORKLOAD CLASSIFIER wc_bi   WITH (WORKLOAD_GROUP = 'wg_bi',   MEMBERNAME = 'pbi_reader', IMPORTANCE = NORMAL);

Smaller per-request grants for BI allow more concurrent queries; larger grants for loads give them memory. Watch sys.dm_workload_management_workload_groups_stats.

4. Caching and pre-computation

ALTER DATABASE dwprod SET RESULT_SET_CACHING ON;       -- run in master
CREATE MATERIALIZED VIEW mv_sales_by_day WITH (DISTRIBUTION = HASH(store_id))
AS SELECT store_id, order_date, SUM(amount) AS amount, COUNT_BIG(*) AS cnt FROM fact_sales GROUP BY store_id, order_date;
  • Result set caching serves repeated identical queries (dashboards) without using compute.
  • Materialized views are maintained automatically and used by the optimiser for matching aggregations.

5. Healthy columnstore and statistics

-- row groups that are small or open (poor compression)
SELECT t.name, rg.state_desc, COUNT(*) AS row_groups, AVG(rg.total_rows) AS avg_rows
FROM sys.dm_pdw_nodes_db_column_store_row_group_physical_stats rg
JOIN sys.pdw_nodes_tables nt ON rg.object_id = nt.object_id AND rg.pdw_node_id = nt.pdw_node_id AND rg.distribution_id = nt.distribution_id
JOIN sys.pdw_table_mappings tm ON nt.name = tm.physical_name
JOIN sys.tables t ON tm.object_id = t.object_id
GROUP BY t.name, rg.state_desc ORDER BY avg_rows;
  • Rebuild indexes on tables with many small/open row groups after loads (ALTER INDEX ALL ON fact_sales REBUILD).
  • Load with larger resource classes/groups so row groups compress fully.
  • Update statistics after significant loads; enable auto-create statistics.
  • Fix distribution skew (see the slow queries post).

6. Commitment and future

  • Reserved capacity for steady DWU baselines.
  • For new analytics workloads, compare with Fabric Warehouse or serverless options (see the Synapse to Fabric transformation post).

Tip

Many pools run at a high DWU because one nightly job needs it. Scale up only for that window from the pipeline, and run the rest of the day at a fraction of the cost.

Checklist

  • 30-day usage pattern reviewed
  • Pause/resume and scaling scheduled or pipeline-driven
  • Workload groups and classifiers for loads vs BI
  • Result set caching and materialized views for repeated queries
  • Columnstore quality and statistics maintained
  • Reservations for steady baseline

Related fixes