Welcome to ExamTopics
ExamTopics Logo
- Expert Verified, Online, Free.
Mail Us[email protected]
Menu

Unlimited Access

Get Unlimited Contributor Access to the all ExamTopics Exams!
Take advantage of PDF Files for 1000+ Exams along with community discussions and pass IT Certification Exams Easily.
Microsoft Discussions

Exam DP-203 topic 1 question 37 discussion

Actual exam question from Microsoft's DP-203
Question #: 37
Topic #: 1
[All DP-203 Questions]

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?

  • A. 40
  • B. 240
  • C. 400
  • D. 2,400
Show Suggested Answer Hide Answer
Suggested Answer: A 🗳️
Each partition should have around 1 millions records. Dedication SQL pools already have 60 partitions.
We have the formula: Records/(Partitions*60)= 1 million
Partitions= Records/(1 million * 60)
Partitions= 2.4 x 1,000,000,000/(1,000,000 * 60) = 40
Note: 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, if you create a table with 100 partitions, the result will be 6000 partitions.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool
by Aslam208 at Dec. 11, 2021, 6 p.m.

Comments

Chosen Answer:
This is a voting comment (?) , you can switch to a simple comment.
Switch to a voting comment New
Submit Cancel
Aslam208
Highly Voted 2 years, 6 months ago
correct
upvoted 25 times
...
dom271219
Highly Voted 1 year, 10 months ago
Selected Answer: A
2,4bn/60=40M
upvoted 13 times
...
e56bb91
Most Recent 11 hours, 13 minutes ago
Selected Answer: A
2.4B = 2400M 2400/60 = 40
upvoted 1 times
...
Danweo
1 day, 18 hours ago
Selected Answer: A
A is correct
upvoted 1 times
...
hassexat
10 months ago
Selected Answer: A
2,400,000,000 / 60,000,000 = 40
upvoted 2 times
...
kkk5566
10 months, 1 week ago
Selected Answer: A
A is correct
upvoted 1 times
...
akhil5432
11 months ago
Selected Answer: A
OPTION A
upvoted 1 times
...
AZLearn111
1 year, 4 months ago
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.
upvoted 12 times
zekescookies
1 year, 2 months ago
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.
upvoted 4 times
...
...
vrodriguesp
1 year, 5 months ago
Selected Answer: A
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
upvoted 3 times
vrodriguesp
1 year, 5 months ago
1 partion : 60M = x : 2.4 B 1 partion : 60 M = x : 2400 M ==> x = 2400 / 60 ==> x = 40 partitions
upvoted 3 times
...
...
SHENOOOO
1 year, 5 months ago
Selected Answer: A
Correct Answer
upvoted 1 times
...
NORLI
1 year, 9 months ago
Very simple go with the smallest partition because too many partitions affect peformance
upvoted 4 times
...
Deeksha1234
1 year, 10 months ago
Selected Answer: A
correct
upvoted 1 times
...
hm358
2 years ago
Selected Answer: A
Optimal distribution is up to 60 instances
upvoted 1 times
...
sdokmak
2 years, 1 month ago
Selected Answer: A
quick maths
upvoted 3 times
...
MS_Nikhil
2 years, 2 months ago
Selected Answer: A
A is correct
upvoted 1 times
...
Egocentric
2 years, 2 months ago
correct
upvoted 1 times
...
Twom
2 years, 3 months ago
Selected Answer: A
Correct
upvoted 2 times
...
Load full discussion...

Get IT Certification

Unlock free, top-quality video courses on ExamTopics with a simple registration. Elevate your learning journey with our expertly curated content. Register now to access a diverse range of educational resources designed for your success. Start learning today with ExamTopics!

Start Learning for free
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
ex Want to SAVE BIG on Certification Exam Prep?
close
ex Unlock All Exams with ExamTopics Pro 75% Off
  • arrow Choose From 1000+ Exams
  • arrow Access to 10 Exams per Month
  • arrow PDF Format Available
  • arrow Inline Discussions
  • arrow No Captcha/Robot Checks
Limited Time Offer
Ends in

Claim Offer Now
286 people claimed it in the last 6 hours