You are designing a partition strategy for a fact table in an Azure Synapse Analytics dedicated SQL pool. The table has the following specifications:
✑ Contain sales data for 20,000 products.
Use hash distribution on a column named ProductID.
✑ Contain 2.4 billion records for the years 2019 and 2020.
Which number of partition ranges provides optimal compression and performance for the clustered columnstore index?
For optimal compression and performance, it's important to have around 1 million rows per partition in a clustered columnstore index. Since the table will contain 2.4 billion records, and given that Azure Synapse Analytics dedicated SQL pools automatically partition data into 60 units, the calculation to determine the number of partitions is straightforward. Dividing the total number of records (2.4 billion) by the number of distributions (60) results in 40 million rows per distribution. To achieve the optimal 1 million rows per partition, each distribution should be divided into 40 partitions. Thus, the correct number of partitions is 40.
correct
2,4bn/60=40M
No of automatic Distributions is 60. So each distribution will have 2.4 B / 60 = 40M. For a good performance each partition within a distribution ( some time called buckets of data ) should have 1M rows per bucket. So 40M / 1M = 40 partitions.
Another way to think about this: The number of records for the period stated = 2.4 billion Number of underlying ("automatic") distributions: 60 2.4 billion / 60 distributions = 40 million rows 40 million / 40 partitions = 1 million rows As stated, 1 million rows per distribution are optimal for compression and performance. Divide the 40 million rows with the other partitioning options and you have too few rows per distribution -> suboptimal.
Very simple go with the smallest partition because too many partitions affect peformance
Considering that: Having too many partitions can reduce the effectiveness of clustered columnstore indexes if each partition has fewer than 1 million rows. Dedicated SQL pools automatically partition your data into 60 databases So a table with no partiton (or just one partition) has 60Milion of records I have use this logic, simple proportion: 1 partion : 60M = x = 2.4 B ==> 1 : 60 M = x : 2400 M ==> x = 2400 / 60 ==> x = 40 partitions
1 partion : 60M = x : 2.4 B 1 partion : 60 M = x : 2400 M ==> x = 2400 / 60 ==> x = 40 partitions
quick maths
2,400,000,000 / 60,000,000 = 40
Correct
2.4B = 2400M 2400/60 = 40
A is correct
A is correct
OPTION A
Correct Answer
correct
Optimal distribution is up to 60 instances
A is correct
correct