The short answer
The cost-based in Azure Synapse relies entirely on statistical metadata to create efficient execution plans. Statistics capture the distribution, density, and of values within table columns.
Why statistics matter in an MPP engine
In an database, the optimizer must make high-stakes physical execution decisions:
- Should it broadcast a table to all 60 distributions (
BroadcastMoveOperation), or should it redistribute both tables across the network (ShuffleMoveOperation)? - Should it execute a hash join or a nested loop?
If statistics are missing or stale after loading 20 million rows, the optimizer may believe a table has only 1,000 rows. It will choose to broadcast the 20-million-row table across the network to all compute nodes. This network flood exhausts cluster bandwidth and causes query execution times to jump from seconds to hours.
Automated versus manual statistics
Synapse Dedicated Pool includes AUTO_CREATE_STATISTICS, which automatically builds single-column statistics when a query references a column in a WHERE or JOIN .
However, AUTO_UPDATE_STATISTICS is not enabled by default in all configurations, and automatic creation does not capture multi-column statistics.
-- Create multi-column statistics for composite join keys
CREATE STATISTICS stat_sales_date_customer
ON dbo.fact_sales(order_date, customer_id);
-- Update statistics explicitly after daily batch loading
UPDATE STATISTICS dbo.fact_sales;In production pipelines, always include an explicit UPDATE STATISTICS step on updated tables immediately following major batch loads, before opening the tables to downstream reporting queries.