EDW Concepts

MPP (massively parallel processing) Database

• https://www.sisense.com/glossary/mpp-database/

Table Distribution Options

Reference

• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

• https://techcommunity.microsoft.com/t5/datacat/choosing-hash-distributed-table-vs-round-robin-distributed-table/ba-p/305247

Hash Distribution

Hash Function and Properties

• A hash function is any function that can be used to map data of arbitrary size to fixed-size

• A hash function is a mathematical function that converts a input value into another compressed numerical value.

• The input to the hash function is of arbitrary length but output is always of fixed length.

• Properties

  1. Efficiently computable.

  2. Should uniformly distribute the keys (Each table position equally likely for each key)

• https://www.tutorialspoint.com/cryptography/cryptography_hash_functions.htm

When to use Hash Function for Distribution

Round Robin Distribution

Replicated

Data Movement

• ShuffleMoveOperation: Redistributes data from one distributed table to another distributed table, changing the distribution column.

• PartitionMoveOperation: Data moved from distributions to Control Node. Usually for Aggregations.

• BroadcastMoveOperation: When a distributed table needs to become replicated for join compatibility

• TrimMoveOperation: When a replicated table needs to become distributed

• MoveOperationData: Moved from Control Node back to Compute Nodes resulting in a replicated table for further processing.

• RoundRobinMoveOperation: Redistributes data to Round Robin Table.

Statistics

Reference Links

• https://www.red-gate.com/simple-talk/databases/sql-server/learn/statistics-in-sql-server/
• https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/#:~:text=SQL%20Server%20statistics%20are%20essential%20for%20the%20query,cardinality%29.%20The%20query%20optimizer%20should%20be%20updated%20regularly.
• https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/sql-server-statistics-basics/

Optimization with Index

• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json&bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json&view=sql-server-ver16&preserve-view=true&viewFallbackFrom=azure-sqldw-latest

Reference Links

• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver16

Clustered Column Index

Instead of storing an entire row or rows in a page, one column from many rows is stored in that page. It is this difference in architecture that gives the columnstore index a very high level of compression along with reducing the storage footprint and providing massive improvements in read performance.

Relevant Links:

• https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/what-are-columnstore-indexes/
• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-ver16
• https://www.patrickkeisler.com/2014/04/what-is-non-clustered-columnstore-index.htm
• https://www.tutorialsteacher.com/sqlserver/indexes#:~:text=%20Create%20a%20Clustered%20Index%20using%20Table%20Designer,table%20name%20from%20the%20File%20menu.%20More%20

Heap

• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index

Clustered

Secondary