urgent - synapse partition table no performance difference?

ash b 1 Reputation point
2021-01-18T17:00:28.667+00:00

one of synapse table we've 300 million rows and keep increasing. Every row as status column i.e active_row either 0 or 1. Active_row is int datatype. Users only query based active_row = 1 which has only 28 million row and rest of data i.e 270 million is inactive.
To increase the performance and avoid to full tablescan on active_row, i've converted the table in partition table on active_row as below

CREATE TABLE [repo].[STXXXXX]
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED INDEX (
[ID] ASC
),
PARTITION
(
active_Row RANGE LEFT FOR VALUES (0,1)
)
)
as
select * from repo.nonptxx;

Users reported there is no performance improvement after moving to partition table. when i checked the below query i.e partition vs non-partition i don't see any difference in query explain plain interms of estimated sub tree, operation etc and all stats remain same figure. From sys.dm_pdw_nodes_db_partition_stats i can see 3 partition created on partition 1 having 270 million data spilt in 60 nodes and partition 2 of 60 nodes 30 million spilted and partition 3 of 60 nodes is empty.

> select * from [repo].[STXXXXX] where active_row =1

vs

> select * from repo.nonptxx where active_row =1

Please advise what's wrong and why there is no improvement after moving into partition table and how to tune it?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,355 questions
{count} votes