Synapse SQL 池中使用複製資料表的設計指引

本文提供在 Synapse SQL 池式結構中設計複製資料表的建議。 利用這些建議來提升查詢效能，減少資料移動與查詢複雜度。

先決條件

本文假設你熟悉 SQL 池中的資料分發與資料移動概念。 更多資訊請參閱 建築 相關文章。

作為表格設計的一部分，盡可能了解你的資料以及資料如何被查詢。  舉例來說，請考慮以下問題：

• 桌子有多大？
• 資料表多久刷新一次？
• 我的 SQL 集區中是否有事實資料表和維度資料表？

什麼是複製表？

複製的表格在每個計算節點上都能存取完整副本。 複製資料表可免除在連接或聚合前於計算節點間傳輸資料的需求。 由於資料表有多份副本，當壓縮後資料表大小小於 2 GB 時，複製表效果最佳。 2GB 並不是硬性限制。 如果資料是靜態且不會改變，你可以複製更大的資料表。

下圖顯示每個 Compute 節點可存取的複製表。 在 SQL 池中，複製的表格會完整複製到每個運算節點的分發式資料庫。

複寫資料表非常適合星型結構描述中的維度資料表。 維度表通常與事實表連接，事實表的分布方式與維度表不同。 尺寸通常適合儲存和維護多份副本。 維度儲存描述性資料，這些資料變化緩慢，例如顧客姓名與地址，以及產品細節。 資料緩慢變化的特性導致對複製資料表的維護需求降低。

考慮在以下情況下使用複製表格：

• 磁碟上的表格大小都少於 2 GB，無論列數多少。 要查詢資料表大小，您可以使用 DBCC PDW_SHOWSPACEUSED 指令： DBCC PDW_SHOWSPACEUSED('ReplTableCandidate')。
• 此資料表用於聯結，而這些聯結原本會需要資料移動。 當將未分布在同一欄位的表格（例如雜湊分布表）與輪詢表連接時，必須進行資料移動才能完成查詢。 如果其中一個資料表很小，考慮一個複製的表格。 在大多數情況下，我們建議使用複寫資料表，而非輪詢配置資料表。 若要在查詢計畫中查看資料移動操作，請使用 sys.dm_pdw_request_steps。 BroadcastMoveOperation 是典型的資料移動操作，可透過使用複製資料表來消除。

複製資料表在以下情況下可能無法產生最佳查詢效能：

• 該表格有頻繁的插入、更新與刪除操作。 資料操作語言（DML）操作需要重建複製的表格。 頻繁重建會導致性能變慢。
• SQL 池經常被擴展。 擴展 SQL 池會改變計算節點數量，這會需要重建複製的表格。
• 資料表有大量欄位，但資料操作通常只存取少量欄位。 在這種情況下，與其複製整個資料表，不如先分散資料表，然後在經常存取的欄位建立索引，效果可能更好。 當查詢需要資料移動時，SQL 池只會移動請求欄位的資料。

使用帶有簡單查詢條件的複製資料表

在你決定分發或複製一個資料表之前，先思考你打算對該資料表執行哪些類型的查詢。 只要可能，

• 對於具有簡單查詢述詞的查詢，例如等於或不等於，請使用複寫資料表。
• 對於具有複雜查詢謂詞（如 LIKE 或 NOT LIKE）的查詢，使用分散式資料表。

CPU 密集查詢在工作分散至所有運算節點時表現最佳。 例如，對資料表每一列執行計算的查詢，在分散式資料表上的表現優於複製資料表。 由於複製的資料表在每個計算節點上完整儲存，對複製資料表進行的 CPU 密集型查詢會在每個計算節點上運行整個資料表的查詢。 額外的運算會拖慢查詢效能。

例如，此查詢具有複雜的述詞。 當資料在分散式資料表中而非複製資料表時，它執行得更快。 在此範例中，資料可採輪詢配置。

SELECT EnglishProductName
FROM DimProduct
WHERE EnglishDescription LIKE '%frame%comfortable%';

將現有的輪詢配置資料表轉換為複寫資料表

如果您已經有循環分配表，我們建議在符合本文所述條件的情況下，將其轉換為複製表。 複寫資料表可消除資料移動需求，因此效能優於輪詢配置資料表。 輪詢配置資料表在聯結時一律需要資料移動。

此範例使用 CTAS 將資料表改 DimSalesTerritory 為複製資料表。 無論 DimSalesTerritory 是雜湊分散還是輪詢配置，此範例都適用。

CREATE TABLE [dbo].[DimSalesTerritory_REPLICATE]
WITH
  (
    HEAP,  
    DISTRIBUTION = REPLICATE  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]
OPTION  (LABEL  = 'CTAS : DimSalesTerritory_REPLICATE')

-- Switch table names
RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[DimSalesTerritory_REPLICATE] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

輪詢配置與複寫的查詢效能範例

複製的表格不需要任何資料移動來進行連線，因為整個表格已經存在於每個計算節點上。 若維度表採用循環分配策略，聯結時會將完整的維度表複製到每個計算節點。 要移動資料，查詢計畫包含一個稱為 BroadcastMoveOperation 的操作。 這種資料移動操作會降低查詢效能，並透過使用複製資料表來消除。 要查看查詢計畫步驟，請使用 sys.dm_pdw_request_steps 系統目錄檢視。

例如，在下列對 AdventureWorks 結構描述執行的查詢中，FactInternetSales 資料表採用雜湊分散。 DimDate和 DimSalesTerritory 表格是較小的維度表。 此查詢回傳2004財政年度北美的總銷售數據：

SELECT [TotalSalesAmount] = SUM(SalesAmount)
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimDate d
  ON d.DateKey = s.OrderDateKey
INNER JOIN dbo.DimSalesTerritory t
  ON t.SalesTerritoryKey = s.SalesTerritoryKey
WHERE d.FiscalYear = 2004
  AND t.SalesTerritoryGroup = 'North America'

我們將DimDate和DimSalesTerritory重新設計為輪詢表。 因此，查詢顯示了以下查詢計畫，包含多個廣播移動操作：

我們重新建立了 DimDate 和 DimSalesTerritory 作為複製的資料表，並再次執行查詢。 產生的查詢計畫短得多，而且沒有任何廣播移動。

修改複製資料表的效能考量

SQL 池透過維護一個主版本的表格來實作複本表格。 它會將主版本複製到每個 Compute 節點的第一個發行資料庫。 當有變更時，先更新主版本，然後重建每個計算節點的表格。 重建複製資料表的過程包括將資料表複製到每個計算節點，然後建立索引。 例如，DW2000c 上的複製資料表有五個資料副本。 每個計算節點上都有一個主控副本和一個完整複本。 所有資料都儲存在分發資料庫中。 SQL 池利用此模型支援更快速的資料修改語句與彈性的擴展操作。

在下列情況之後，第一次對複寫資料表執行查詢時，系統會觸發非同步重建：

• 資料會被載入或修改
• Synapse SQL 實例則被擴展到另一個層級
• 表格定義已更新

以下情況下無需重建：

• 暫停操作
• 恢復營運

重建不會在資料修改後立即進行。 相反地，系統會在查詢第一次從資料表選取資料時觸發重建。 觸發重建的查詢會立即從主版本的資料表讀取，同時資料會非同步複製到每個計算節點。 在資料複製完成前，後續查詢仍會使用資料表的主版本。 如果有任何操作針對複製的資料表，迫使重新建構，資料副本將會失效，下一次執行 select 語句時會再度觸發資料複製。

保守使用索引

標準索引作業適用於複製的資料表。 SQL 池會重建每個複製的表格索引，作為重建的一部分。 只有當效能提升超過重建指數的成本時，才使用索引。

批次資料載入

在將資料載入複寫式資料表時，盡量透過批次載入來減少重建。 在執行 select 語句前，先執行所有批次載入。

例如，此載入模式會從四個來源載入資料，並呼叫四次重建。

• 從來源 1 載入。
• Select 陳述式觸發重建 1。
• 從來源2載入。
• Select 陳述式觸發重建 2。
• 從來源3載入。
• Select 陳述式觸發重建 3。
• 從來源4載入。
• Select 陳述式觸發重建 4。

例如，這個載入模式會載入來自四個來源的資料，但只會呼叫一次重建。

• 從來源 1 載入。
• 從來源2載入。
• 從來源3載入。
• 從來源4載入。
• Select 陳述式觸發重建。

批次載入後重建複製表

為了確保查詢執行時間一致，建議在批次載入後強制建置複製的表格。 否則，第一個查詢仍會使用資料移動來完成查詢。

「建置複寫的資料表快取」作業最多可以同時執行兩個作業。 例如，如果您嘗試重建五個資料表的快取，系統會使用 staticrc20 (無法修改) 同時建置兩個資料表。 因此，建議避免使用超過 2 GB 的大型複製資料表，因為這可能會拖慢節點間的快取重建速度，並延長整體時間。

此查詢使用 sys.pdw_replicated_table_cache_state DMV 列出已修改但未重建的複製資料表。

SELECT SchemaName = SCHEMA_NAME(t.schema_id)
 , [ReplicatedTable] = t.[name]
 , [RebuildStatement] = 'SELECT TOP 1 * FROM ' + '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.[name] +']'
FROM sys.tables t 
JOIN sys.pdw_replicated_table_cache_state c 
  ON c.object_id = t.object_id
JOIN sys.pdw_table_distribution_properties p
  ON p.object_id = t.object_id
WHERE c.[state] = 'NotReady'
AND p.[distribution_policy_desc] = 'REPLICATE'

要觸發重建，請對前一個輸出中的每個資料表執行以下陳述。

SELECT TOP 1 * FROM [ReplicatedTable]

-- Incorrect sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
SELECT TOP 1 * FROM [ReplicatedTable]

UPDATE STATISTICS [ReplicatedTable]
END

-- Correct sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
UPDATE STATISTICS [ReplicatedTable]

SELECT TOP 1 * FROM [ReplicatedTable]
END

要監控重建過程，可以使用 sys.dm_pdw_exec_requests，其中會 command 以「BuildReplicatedTableCache」開頭。 例如：

-- Monitor Build Replicated Cache
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE command like 'BuildReplicatedTableCache%'

下一步

要建立複製資料表，請使用以下其中一種語句：

• 建立資料表（SQL 池）
• 以 SELECT 建立資料表（SQL 池）

關於分散式資料表的概述，請參見分散式資料表。