Comments on: Azure Synapse Analytics : Choose Right Index and Partition (Dedicated SQL Pools)

By: Ash

Ash — Wed, 20 Jan 2021 15:18:52 +0000

Nice article. In one of synapse table we’ve 500 millions rows and distributed in round_robin with cluster index. In that table, every row has record_status of 1 or 0 in int column data type. Only user queries record_status =1 which has only 30 million and 0 status about 470 million for archive purpose. To avoid the full table scan of record_status column, i’ve created below partition tablewith same distribution and clustered index

CREATE TABLE [repo].[pts_part]
WITHc
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED INDEX (
[ID] ASC
),
PARTITION
(
record_status RANGE LEFT FOR VALUES (0,1)
)
)
as
select * from [repo].[pts]

When i checked the runtime and explained plan i.e estimated subtree etcof below both queries. it remain same

select count(*) from [repo].[pts] where record_status =1
select count(*) from [repo].[pts_part] where record_status =1

kindly advise what wrong and how to tune it? Appreciate your reply

LikeLike

By: Things to Know About Serverless SQL Pool (Built-in pool) in Azure Synapse Analytics | @Subaru

Tue, 10 Nov 2020 00:18:45 +0000

[…] performance perspective, it will be recommended to use Apache Parquet (columnar-base format), but there exist another reason for using Apache Parquet in Serverless SQL […]

LikeLike

By: Azure Synapse Analytics : How Statistics and Cache Works (Dedicated SQL Pools) – tsmatz

Fri, 30 Oct 2020 00:24:40 +0000

[…] …) will be cached in some conditions. For instance, CCI tables (see my previous post “Azure Synapse Analytics : Choose Right Index and Partition” for CCI) will locally cache the recently-used columnstore segments on distributed compute […]

LikeLike

By: Azure Updates (2020.10.22) | ブチザッキ

Azure Updates (2020.10.22) | ブチザッキ — Wed, 21 Oct 2020 18:59:05 +0000

[…] Azure Synapse Analytics : Choose Right Index and Partition (Dedicated SQL Pools) […]

LikeLike

By: Choosing the Right Index and Partition in Dedicated SQL Pools – Curated SQL

Wed, 21 Oct 2020 12:10:41 +0000

[…] Tsuyoshi Matsuzaki gives us some advice on indexing and partitioning data in Azure Synapse Analytics…: […]

LikeLike