Microsoft design guidance suggests using a replicated distribution for small dimensions, such as date. For small service tiers, e.g., less than DW1000 where there is only one compute node allocated, does this selection still have positive impacts on performance? Or is it essentially the same as round robin until a second compute node is added?

Hi @Matt-7999 , Thanks for posting the question. The number of distributions is 60 for one compute node as per link below. Compute node increases the processing power where as replicate, round robin are used to determine distribution. memory-concurrency-limits Sharing one more useful link found for Synapse in the similar context below loading-data-in-azure-synapse-using-copy Hope this helps!

Replicated Table Distribution for Small Deployment

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-07-14T09:25:40.017+00:00

Hi @Matt-7999,

Thanks for posting the question. The number of distributions is 60 for one compute node as per link below. Compute node increases the processing power where as replicate, round robin are used to determine distribution.

memory-concurrency-limits

Sharing one more useful link found for Synapse in the similar context below

loading-data-in-azure-synapse-using-copy

Hope this helps!
Please sign in to rate this answer.

1 person found this answer helpful.
Matt 21 Reputation points

2020-07-14T13:25:52.927+00:00

While I appreciate the reply @HarithaMaddi-5526 , I don't think it sufficiently answered my question. Let me try to re-state it:

Documentation states that the REPLICATE sharding pattern is useful because it stores a full copy of the table on each compute node, which can limit data movement between nodes. My question is--for small distributions such as DW500, where we are only allocated a single compute node, are there any benefits to this REPLICATE sharding pattern as opposed to HASH or ROUND ROBIN distribution, or is it only worth using in higher tiers where more compute nodes are allocated?

HarithaMaddi-MSFT 10,136 Reputation points

2020-07-14T14:02:12.997+00:00

Hi @Matt-7999,

Thanks for the clarification, I will get back to you with more info as soon as possible.

HarithaMaddi-MSFT 10,136 Reputation points

2020-07-14T15:29:23.217+00:00

Hi @Matt ,

Yes Matt, I validated and observed that Replication and Round Robin behave in the same way in single compute node. The benefit of Replication can be observed for limiting data movement in multiple compute node environments only.

Hope this clarifies the query.

Matt 21 Reputation points

2020-07-15T21:50:31.027+00:00

Thanks for looking into this for me!

I think it would be useful to update the Microsoft Guidance documentation for this if you have an opportunity:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/design-guidance-for-replicated-tables

In some YouTube videos for certification training, MS instructor incorrectly claims this replicates over all 60 partitions. In the GA announcement for this feature, I think it was shown that there was improvement on a small DW deployment (but I think at this point in time, there were more compute nodes on those same numbered tier deployments).

I just mean to say, that seeking this info wasn't easy, so explicitly stating it on the guidance documentation for the feature might help others down the line. Thanks!

HarithaMaddi-MSFT 10,136 Reputation points

2020-08-12T08:08:24.613+00:00

Apologies @Matt for delay in responding to your comment. I understand the challenge in getting the clarity for the question, please raise a doc issue as below on the Page and we can take it to content owner for enhancing it with those details.
Sign in to comment

Share via

Replicated Table Distribution for Small Deployment

0 additional answers