0

I will give a few examples of the tables we have:

Table1 - Most queries are State Code and Year, has about 1000 rows, will grow by 1000 rows a year, used Round Robin

Table2 - Most queries are by State Code, Year, Column 1 (String), has about 1000 rows, will grow by 1000 rows a year, used Round Robin

Table3 - Most queries are by State Code and Year, has about 100,000 rows, will grow by about 25000 rows a year, used Hash

Table4 - Most queries are by Year, has about 100,000 rows, will grow by about 25000 rows a year, plan to use Hash

Lookup Table 5 - Most queries are by State Code and Look up Id, has about 10 rows, used Replication

Lookup Table 6 - Most queries are by State Code and Look pup Id, has about 500 rows, used Replication

Did we use the correct types of distribution? Can someone give a more concrete/better example/clarification of when/why you should use each type of distribution? Microsoft's documentation/guidance wasn't very helpful.

Improve this question
2
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Dec 20, 2022 at 21:31
  • I'm asking for some guidance. We read the Microsoft Documentation and want to know if we applied the correct solution.
    – xmlapi
    Dec 20, 2022 at 22:15

1 Answer 1

Reset to default
0

For tables of this size you shouldn't use Synapse Dedicated SQL pool. Per the Synapse guidance all those tiny tables should be Replicated, which is a strong indication that your data is too small for a Massively Parallel database system like Synapse SQL Pool.

Use Azure SQL Database instead, scales down to less than 1 core, and up to around 80 cores, plus additional readable replicas, and which supports columnstore tables, and partitioning.

Improve this answer
2
  • Interesting, thank you for some guidance. However, what about the tables that are expected to grow by at least 200k every year?
    – xmlapi
    Dec 20, 2022 at 23:02
  • Still tiny. Nowhere near needing an MPP database.
    – David Browne - Microsoft
    Dec 20, 2022 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.