Azure Synapse Analytics 中專用 SQL 集區的最佳做法

  • 發行項

本文提供最佳做法的集合，可協助您達到 Azure Synapse Analytics 中專用 SQL 集區的最佳效能。 如果您使用無伺服器 SQL 集區，請參閱 無伺服器 SQL 集區的最佳做法以取得特定指引。 接下來，您會在建置解決方案時，找到要專注於的基本指引和重要領域。 每個區段都會向您介紹概念，然後指向更深入地涵蓋概念的更詳細文章。

專用 SQL 集區載入

如需專用 SQL 集區載入指引，請參閱 載入數據的指引。

使用暫停和調整來降低成本

如需透過暫停和調整來降低成本的詳細資訊，請參閱 管理計算。

維護統計數據

專用 SQL 集區可以設定為自動偵測及建立數據行的統計數據。 優化器所建立的查詢計劃只與可用的統計數據一樣好。

建議您為資料庫啟用AUTO_CREATE_STATISTICS，並讓統計數據每日或每次載入之後保持更新，以確保查詢中使用的數據行統計數據一律為最新狀態。

若要縮短統計數據維護時間，請選擇性地瞭解哪些數據行具有統計數據，或需要最頻繁的更新。 例如，您可能想要更新每天新增值之日期數據行。 將焦點放在擁有聯結相關數據行的統計數據、WHERE 子句中使用的數據行，以及 GROUP BY 中找到的數據行。

如需統計數據的其他資訊，請參閱管理數據表統計數據、CREATE STATISTICS 和 UPDATE STATISTICS 文章。

調整查詢效能

將 INSERT 語句分組成批次

使用 INSERT 語句的一次性載入小型數據表，例如 INSERT INTO MyLookup VALUES (1, 'Type 1')，視您的需求而定，可能是最佳方法。 不過，如果您需要全天載入數千或數百萬個數據列，單一 INSERTS 可能不是最佳的。

解決此問題的其中一種方法是開發一個寫入檔案的程式，然後開發另一個程式來定期載入此檔案。 如需詳細資訊， 請參閱 INSERT 文章。

使用 PolyBase 快速載入和導出數據

專用 SQL 集區支援透過數個工具載入和匯出數據，包括 Azure Data Factory、PolyBase 和 BCP。 對於效能不重要的少量數據，任何工具可能都足以滿足您的需求。

注意

當您載入或匯出大量數據，或需要更快的效能時，PolyBase 是最佳選擇。

PolyBase 載入可以使用 CTAS 或 INSERT INTO 來執行。 CTAS 會將事務歷史記錄降到最低，而且是載入數據最快的方式。 Azure Data Factory 也支援 PolyBase 載入，而且可以達到類似 CTAS 的效能。 PolyBase 支援各種檔案格式，包括 Gzip 檔案。

若要在使用 Gzip 文字檔時將輸送量最大化，請將檔案分成 60 個以上的檔案，以將載入的平行處理原則最大化。 若要加快總輸送量，請考慮同時載入數據。 本節的其他資訊包含在下列文章中：

載入然後查詢外部資料表

PolyBase 不適用於查詢。 專用 SQL 集區的 PolyBase 數據表目前僅支援 Azure Blob 檔案和 Azure Data Lake 記憶體。 這些檔案沒有任何備份的計算資源。 因此，專用 SQL 集區無法卸除這項工作，而且必須藉由載入它來 tempdb 讀取整個檔案，以便讀取數據。

如果您有數個查詢查詢此數據，最好一次載入此數據，並讓查詢使用本機數據表。 進一步的 PolyBase 指引包含在 使用 PolyBase 指南一文中。

哈希散發大型數據表

根據預設，數據表會散發迴圈配置資源。 此預設值可讓用戶輕鬆地開始建立數據表，而不需要決定其數據表的散發方式。 迴圈配置資源數據表可能會針對某些工作負載執行得足夠。 但是，在大部分情況下，散發數據行可提供更佳的效能。

由迴圈配置資源數據表的數據行散發的數據表最常見的範例，就是聯結兩個大型事實數據表。

例如，如果您有依order_id散發的訂單數據表，而交易數據表也由order_id散發，當您將訂單數據表聯結至order_id上的交易數據表時，此查詢會變成傳遞查詢。 然後會排除數據移動作業。 較少的步驟表示更快速的查詢。 較少的數據移動也可讓您更快速地進行查詢。

提示

載入分散式數據表時，您的傳入數據不應該在散發索引鍵上排序。 這樣做會減緩您的負載。

下面提供的文章連結會提供您透過選取散發數據行來改善效能的其他詳細數據。 此外，您會發現如何在 CREATE TABLE 語句的 WITH 子句中定義分散式數據表的相關信息：

不要過度分割

雖然數據分割數據可透過分割區切換或透過數據分割消除來優化掃描來維護數據，但分割區過多可能會減緩查詢的速度。 通常，在 SQL Server 上運作良好的高粒度分割策略，可能無法在專用 SQL 集區上運作良好。

如果每個數據分割的數據列少於100萬個數據列，則擁有太多數據分割可以降低叢集資料行存放區索引的有效性。 專用 SQL 集區會自動將數據分割成 60 個資料庫。 因此，如果您建立具有100個分割區的數據表，結果會是6000個數據分割。 每個工作負載都不同，因此最好的建議是實驗數據分割，以查看最適合您工作負載的內容。

考慮的其中一個選項是使用低於您使用 SQL Server 實作的數據粒度。 例如，請考慮使用每周或每月分割區，而不是每日數據分割。

有關數據分割的詳細資訊，請參閱 數據表數據分割 一文。

將交易大小降到最低

INSERT、UPDATE 和 DELETE 語句會在交易中執行。 當失敗時，必須回復它們。 若要降低長時間回復的可能性，請盡可能將交易大小降到最低。 將 INSERT、UPDATE 和 DELETE 語句分割成部分，即可將交易大小降至最低。 例如，如果您有預期需要 1 小時的 INSERT，您可以將 INSERT 分成四個部分。 然後，每次執行都會縮短為15分鐘。

提示

利用特殊的最小記錄案例，例如 CTAS、TRUNCATE、DROP TABLE 或 INSERT，以降低回復風險。

另一個消除復原的方法，是使用僅限元數據的作業，例如數據分割切換以進行數據管理。 例如，您可以每月分割數據，而不是執行 DELETE 語句來刪除資料表中order_date為 2001 年 10 月的所有數據列。 然後，您可以從另一個數據表切換空白數據分割的數據分割區（請參閱 ALTER TABLE 範例）。

對於未分割的數據表，請考慮使用 CTAS 來寫入您想要保留在數據表中的數據，而不是使用 DELETE。 如果 CTAS 需要相同的時間，執行會更安全，因為它的事務歷史記錄最少，而且可以視需要快速取消。

本節相關內容的詳細資訊包含在下列文章中：

減少查詢結果大小

減少查詢結果大小可協助您避免因大型查詢結果所造成的客戶端問題。 您可以編輯查詢，以減少傳回的數據列數目。 某些查詢產生工具可讓您將「top N」 語法新增至每個查詢。 您也可以將查詢結果 CETAS 到臨時表，然後使用 PolyBase 匯出進行下層處理。

使用最小可能的數據行大小

定義 DDL 時，請使用支援資料的最小數據類型，因為這樣做會改善查詢效能。 對於 CHAR 和 VARCHAR 數據行而言，這項建議特別重要。 如果數據行中的最長值為 25 個字元，請將數據行定義為 VARCHAR（25）。 請避免將所有字元數據行定義為較大的預設長度。 此外，當這是所有必要的數據，而不是使用 NVARCHAR 時，請將數據行定義為 VARCHAR。

如需與上述資訊相關的基本概念詳細檢閱，請參閱 數據表概觀、 數據表數據類型和 CREATE TABLE 文章。

針對暫時性數據使用暫存堆積數據表

當您暫時在專用 SQL 集區上登陸數據時，堆積數據表通常會讓整體程式更快。 如果您只載入資料以暫存數據，再執行更多轉換，將數據表載入堆積數據表會比將數據載入叢集數據行存放區數據表更快。

將數據載入臨時表也會比將數據表載入永久記憶體更快。 臨時表會以 「#」 開頭，而且只能由建立它的會話存取。 因此，它們只能在有限的案例中運作。 堆積數據表定義於 CREATE TABLE 的 WITH 子句中。 如果您使用臨時表，請記得也在該臨時表上建立統計數據。

如需詳細資訊，請參閱臨時表、CREATE TABLE 和 CREATE TABLE AS SELECT 文章。

優化叢集數據行存放區數據表

叢集數據行存放區索引是您可以將數據儲存在專用 SQL 集區中最有效率的方式之一。 根據預設，專用 SQL 集區中的數據表會建立為叢集數據行存放區。 若要取得數據行存放區數據表上查詢的最佳效能，具有良好的區段品質很重要。 將數據列寫入記憶體壓力下的數據行存放區數據表時，數據行存放區區段品質可能會受到影響。

區段品質可由壓縮數據列群組中的數據列數目來測量。 如需偵測和改善叢集數據行存放區數據表區段品質的逐步指示，請參閱數據表索引一文中數據行存放區索引品質不佳的原因。

由於高質量的數據行存放區區段很重要，因此最好使用中型或大型資源類別中的使用者標識碼來載入數據。 使用較低的 數據倉儲單位 表示您想要將較大的資源類別指派給載入使用者。

數據行存放區數據表通常不會將數據推送至壓縮的數據行存放區區段，直到每個數據表有超過100萬個數據列為止。 每個專用 SQL 集區數據表都會散發到 60 個不同的散發套件。 因此，除非數據表的數據列超過 6000 萬個數據列，否則數據行存放區數據表不會讓查詢受益。

提示

對於少於 6000 萬個數據列的數據表，具有數據行存放區索引可能不是最佳解決方案。

如果您分割數據，每個分割區都必須有1百萬個數據列，才能受益於叢集數據行存放區索引。 對於具有 100 個數據分割的數據表，它至少需要有 60 億個數據列，才能受益於叢集數據行存放區（60 個散發 100 個 分割區 1 百萬個數據列）。

如果您的數據表沒有 60 億個數據列，您有兩個主要選項。 請減少分割區數目，或改為考慮使用堆積數據表。 使用具有次要索引的堆積數據表，而不是數據行存放區數據表，查看是否可以取得更好的效能。

查詢資料行存放區數據表時，如果您只選取所需的數據行，查詢會更快執行。 如需數據表和資料行存放區索引的詳細資訊，請參閱下列文章：

使用較大的資源類別來改善查詢效能

SQL 集區會使用資源群組作為將記憶體配置給查詢的方式。 一開始，所有用戶都會指派給小型資源類別，以授與每個散發 100 MB 的記憶體。 一律有60個散發套件。 每個散發套件至少提供 100 MB。 全系統記憶體配置總計為 6,000 MB，或低於 6 GB。

某些查詢，例如大型聯結或載入叢集數據行存放區數據表，將受益於較大的記憶體配置。 某些查詢，例如純掃描，將看不到任何好處。 使用較大的資源類別會影響並行。 因此，在將所有使用者移至大型資源類別之前，您會想要記住這些事實。

如需資源類別的其他資訊，請參閱 工作負載管理 的資源類別一文。

使用較小的資源類別來增加並行

如果您注意到使用者查詢出現長時間延遲，您的使用者可能會在較大的資源類別中執行。 此案例會提升並行位置的耗用量，這可能會導致其他查詢排入佇列。 若要判斷使用者查詢是否已排入佇列，請執行 SELECT * FROM sys.dm_pdw_waits 以查看是否傳回任何數據列。

工作負載管理和sys.dm_pdw_waits文章的資源類別會為您提供詳細資訊。

使用 DMV 來監視和優化您的查詢

專用 SQL 集區有數個 DMV，可用來監視查詢執行。 下列監視文章會逐步引導您逐步說明如何檢視執行中查詢的詳細數據。 若要快速尋找這些 DMV 中的查詢，請搭配查詢使用 LABEL 選項來協助。 如需其他詳細資訊，請參閱下列清單中所包含的文章：

下一步

另請參閱 疑難解答 文章以瞭解常見問題和解決方案。

如果您需要本文中未提供的資訊，請搜尋 Azure Synapse 的 Microsoft Q&A 問題頁面，是您向其他使用者和 Azure Synapse Analytics 產品群組提出問題的地方。

我們積極監視此論壇，以確保您的問題是由其他使用者或我們中的使用者回答。 如果您想要在 Stack Overflow 上提出問題，我們也會有 Azure Synapse Analytics Stack Overflow 論壇。