DISTRIBUTION = REPLICATE Dedicated SQL Pool data not getting replicated

Question

Hi,

We have a table created in Azure Dedicated pool with Distribution = Replicate. But when we check how the data is distributed, we are not seeing same data getting replicated across all nodes uniformly.

Used the command DBCC PDW_SHOWSPACEUSED to check the data distribution.

We have Cube refresh which is taking lot of time. Can some one help us with the possible issue/input as to why data is it not getting replicated.

Regards,
Phaneendra

Answer 1

Hi @Phaneendra Babu Subnivis

Replicated tables are essentially stored as round robin tables then copied to the first distribution on each compute node. So when looking at the output from DBCC PDW_SHOWSPACEUSED you will see the data spread across all 60 distributions. You will see a few distributions (first distribution on each node) will have a greater amount of data stored. You can add WITH IGNORE_REPLICATED_TABLE_CACHE to the DBCC PDW_SHOWSPACEUSED command to show just the data space excluding the replicated copy size. What you are seeing is not that the data isn't being replicated, but just a byproduct of the way replicated tables are implemented.

There is a process that builds the replicated table cache that will show up in sys.dm_pdw_exec_requests with a command called BuildReplicatedTableCache. You can then see the status of the replicated table cache in the DMV sys.pdw_replicated_table_cache_state.

Additional information can be found in the documentation at Design guidance for using replicated tables in Synapse SQL pool.