Azure-docs: Misleading statement "the replicated table is cached on the first distribution on each compute node."

[Robert-Kostecki]

Replicated Table Section reads: "For SQL Data Warehouse, the replicated table is cached on the first distribution on each compute node."
How does only the FIRST distribution on each node CACHE data?

1. Distributions are the way of storing data (across 60 databases). Consequently, a distributed table should be replicated across all 60 distributions and not just the first one.
2. When it comes to caching this should be done by NVME in Gen2, which I'd assume is one per node (and shared by an instance across all the distribution databases?).

I could not find and detail level architecture but looking at different resources I’d assume that above is the case, can you please confirm?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 3c6f95be-15fb-4822-1fa2-4f61ef029093
• Version Independent ID: 5b38f604-088b-8e2e-7545-278801d68e51
• Content: Azure SQL Data Warehouse - MPP architecture
• Content Source: articles/sql-data-warehouse/massively-parallel-processing-mpp-architecture.md
• Service: sql-data-warehouse
• GitHub Login: @ronortloff
• Microsoft Alias: rortloff

[Mike-Ubezzi-MSFT]

@Robert-Kostecki Thank you for the detailed feedback. We are actively investigating and will get back to you soon.

[ronortloff]

Hi @Robert-Kostecki,
Multiple distribution databases are mounted to compute nodes in most DWU configurations. For example, DW2000c has 4 compute nodes and 15 distribution databases per node. Because replicated tables help with reducing data movement between compute nodes, only 1 distribution database needs a full copy of the table per compute node.
You are correct that there is NVME Gen2 cache on each node. However, it is transient and does not survive pause/resume. Furthermore, replicated tables are available on Gen1, which does not have the same architecture as Gen2.
The article https://docs.microsoft.com/en-us/azure/sql-data-warehouse/memory-and-concurrency-limits has compute nodes per DWU setting and distribution databases per compute node configurations.

[Robert-Kostecki]

Hi Ron, interesting debate and I still believe that I may be correct.
Firstly, let's observe that the movement pertains rather to Distributions then Nodes. Consequently, even with ONE Node configuration you may see the "internal" movement (between the distributions).
Secondly if in fact the table was only replicated to the first distribution database on the node then each time you scale up, and as result adding a new node, the DMS would have to intelligently replicate the data (and possibly remove it when you scale-down).
Finally, here’s a fragment from Data Warehouse training where Theresa Iserman (SQL Server Premier Field Engineer at Microsoft) states that (in a 4-node configuration) “the replicated table gets fully copied to all 60 distributions” https://youtu.be/T6mBS_qfFFE?t=62

[ronortloff]

Hi @Robert-Kostecki,
Apologies for the confusion coming from the video you referenced. In a 4-node configuration, the table is not copied to all 60 distributions. I will look into getting the video corrected. Please take a look at the documentation on replicated tables for further details on how they are implemented: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/design-guidance-for-replicated-tables#performance-considerations-for-modifying-replicated-tables. This link goes directly to the section that addresses your question, but feel free to look at the entire document for further details on replicated tables. Hopefully this clears up any questions you may have.

[Robert-Kostecki]

Hi @ronortloff, firstly thank you for the clarification.

The material that I referenced is part of Microsoft 70-776 "Perform Big Data Engineering on Microsoft Cloud Services" Certification" and specifically "Delivering a Data Warehouse in the Cloud" (Microsoft - DAT220x) https://www.edx.org/course/delivering-a-data-warehouse-in-the-cloud-0. I agree that it would be good to get it corrected.

I have just spent some time playing with the distributions and here are my findings:

1. The REPLICATED table is initially loaded in ROUND_ROBIN distribution across all (60) Distributions. No fully replicated copy at this stage no matter how long you wait.

2. First Distribution on each Node only gets FULL data copy after first query is executed against the table. (You may need to wait a moment for DMS to kick off).

Resizing:

1. When you resize and add another node, firstly all distributions drop FULL copy of the data (and keep only the partial copy – see point 1). Then the FULL copy gets repopulated to the first distribution on each Node.

Hope this helps.

Script to replicate the case below.

-- 1. Create Dummy Records in #TempLoad

CREATE TABLE #TmpLoad (ID INT IDENTITY, C1 uniqueidentifier)
WITH
(
DISTRIBUTION = HASH(C1) -- ROUND_ROBIN is faster but let's do a HASH
as to prove that REPLICATE don’t just copy ROUND_ROBIN distribution
, HEAP
)

INSERT INTO #TmpLoad (C1) VALUES ('12345678-1234-1234-1234-123456789012');
INSERT INTO #TmpLoad (C1) VALUES ('12345678-1234-1234-1234-123456789013');

DECLARE @i int = 24

WHILE @i > 0
BEGIN
INSERT INTO #TmpLoad (C1)
SELECT C1 FROM #TmpLoad;
--SELECT NewID() FROM #TmpLoad;

SET @i-=1;

END

-- Check row count and DW size

SELECT COUNT(1) FROM #TmpLoad; -- 134,217,728 33,554,432
DBCC PDW_SHOWSPACEUSED; -- 60x 3384 1248 1336 800 1 1

-- 2. Create Test Dim table (REPLICATE)

CREATE TABLE TDim
WITH (
DISTRIBUTION = REPLICATE
,HEAP
)
AS
SELECT * FROM #TmpLoad;

-- check DW size
-- NOTE data gets populated in ROUND_ROBIN fashion even though it's stored in HASH distribution in the source temp table

DBCC PDW_SHOWSPACEUSED;

-- NOTE the data doesn't get FULLY copied to the first distribution until you run a query against the table

SELECT COUNT(1) FROM TDim;

DBCC PDW_SHOWSPACEUSED;

-- NOTE Now first distribution on the node(s) have both FULL as well as the Partial data

[ronortloff]

Hi @Robert-Kostecki,
Your explanation and code look good. Thanks for sharing.

[Mike-Ubezzi-MSFT]

Thanks for the feedback! I have assigned the issue to the content author to evaluate and update as appropriate.

[Mike-Ubezzi-MSFT]

@Robert-Kostecki We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.