針對專用 SQL 集區上的慢速查詢進行疑難解答

發行項
04/13/2024

適用於：Azure Synapse Analytics

本文可協助您找出原因，並針對 Azure Synapse 分析專用 SQL 集區上查詢的常見效能問題套用風險降低措施。

請遵循步驟來針對問題進行疑難解答，或透過 Azure Data Studio 在筆記本中執行步驟。前三個步驟會逐步引導您收集遙測，其中描述查詢的生命週期。本文結尾的參考可協助您分析在所收集數據中找到的潛在商機。

注意事項

嘗試開啟此筆記本之前，請確定已在本機計算機上安裝 Azure Data Studio。若要安裝它，請移至瞭解如何安裝 Azure Data Studio。

在 Azure Data Studio 中開啟 Notebook

重要事項

大部分回報的效能問題都是由下列原因所造成：

過期的統計數據
CCIs (狀況不良的叢集數據行存放區索引)

若要節省疑難解答時間，請確定已建立統計數據，並重建最新的 CCI和CCI。

步驟 1：識別request_id (也称为 QID)

request_id需要慢速查詢的，才能研究查詢速度緩慢的潛在原因。使用下列腳本作為識別您想要進行疑難解答之查詢的起點。識別出慢速查詢之後，請記下 request_id 值。

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed','Failed','Cancelled')
AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;

-- Find top 10 longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

若要以較慢的查詢為目標，請在執行腳本時使用下列秘訣：

依 submit_time DESC 或 total_elapsed_time DESC 排序，以在結果集頂端有運行時間最長的查詢。
在查詢中使用 OPTION(LABEL='<YourLabel>') ，然後篩選 label 數據行以識別它們。
當您知道目標語句包含在批次中時，請考慮篩選掉任何沒有值 resource_allocation_percentage 的 QID。

注意： 請謹慎使用此篩選條件，因為它可能也會篩選掉其他會話所封鎖的一些查詢。

步驟 2：判斷查詢花費的時間

執行下列腳本，以尋找可能導致查詢效能問題的步驟。使用下表所述的值，更新腳本中的變數。將值變更 @ShowActiveOnly 為 0，以取得分散式方案的完整圖片。記下 StepIndex從結果集中識別之慢速步驟的、 Phase和 Description 值。

參數	描述
`@QID`	在`request_id`步驟 1 中取得的值
`@ShowActiveOnly`	0 - 顯示查詢的所有步驟 1 - 只顯示目前作用中的步驟

DECLARE @QID VARCHAR(16) = '<request_id>', @ShowActiveOnly BIT = 1; 
-- Retrieve session_id of QID
DECLARE @session_id VARCHAR(16) = (SELECT session_id FROM sys.dm_pdw_exec_requests WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked waiting on '
       + MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
                  WHEN waiting.type LIKE 'Shared-%' THEN ''
                  ELSE 'Resource Allocation (Concurrency)' END)
       + MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
             ELSE '' END) AS [Description],
   MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
   AND ([type] LIKE 'Shared-%' OR
      [type] in ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
   AND [state] = 'Queued'
GROUP BY session_id 
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on '
   + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
   waiting.request_time AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, waiting.request_time, GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount],
   COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
FROM sys.dm_pdw_waits waiting
   INNER JOIN sys.dm_pdw_waits blocking
      ON waiting.object_type = blocking.object_type
      AND waiting.object_name = blocking.object_name
   INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
      ON blocking.request_id = blocking_exec_request.request_id
WHERE waiting.session_id = @session_id AND waiting.state = 'Queued'
   AND blocking.state = 'Granted' AND waiting.type != 'Shared' 
-- Request Steps
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, step_index AS [StepIndex],
       'Execution' AS [Phase], operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime], end_time AS [EndTime],
       total_elapsed_time/1000.0 AS [Duration], [status] AS [Status],
       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
   AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;

步驟 3：檢閱步驟詳細數據

執行下列腳本，以檢閱上一個步驟中所識別步驟的詳細數據。使用下表所述的值，更新腳本中的變數。 @ShowActiveOnly將值變更為 0，以比較所有發佈時間。記下 wait_type 可能會造成效能問題的散發值。

參數	描述
`@QID`	在`request_id`步驟 1 中取得的值
`@StepIndex`	步驟 `StepIndex`2 中識別的值
`@ShowActiveOnly`	0 - 顯示指定 `StepIndex` 值的所有散發套件 1 - 只顯示指定 `StepIndex` 值目前作用中的散發套件

DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
       distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
       start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex
    UNION ALL
    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
       distribution_id, pdw_node_id, sql_spid AS spid, [type],
       [status], start_time, end_time, total_elapsed_time, rows_processed as row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
   )
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
   LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
      ON sr.pdw_node_id = owt.pdw_node_id
         AND sr.spid = owt.session_id
         AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
                 AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
              OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
                     AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
      AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
               CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)
           OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
                  CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))
      AND sr.step_index = @StepIndex
ORDER BY distribution_id

步驟 4：診斷和緩解

編譯階段問題

根據步驟 2 中取得的Description值，請查看相關章節，以取得下表的詳細資訊。

描述常見原因

Compilation Concurrency 已封鎖：編譯並行

Resource Allocation (Concurrency) 已封鎖：資源配置

描述	常見原因
`Compilation Concurrency`	已封鎖：編譯並行
`Resource Allocation (Concurrency)`	已封鎖：資源配置

如果查詢處於步驟 1 中識別的「執行中」狀態，但步驟 2 中沒有步驟資訊，請檢查最適合您案例的原因，以從下表取得詳細資訊。

案例	常見原因
語句包含複雜的聯結篩選邏輯，或在子句中 `WHERE` 執行聯結	複雜查詢或較舊的 JOIN 語法
語句是長時間執行 `DROP TABLE` 的或 `TRUNCATE TABLE` 語句	長時間執行的DROP TABLE或TRUNCATE TABLE
CCI 有高百分比的已刪除或開啟資料列 (請參閱優化叢集數據行存放區索引)	狀況不良的CCI (通常)

針對在緩慢查詢提交之後立即執行的一或多個CREATE STATISTICS語句，分析步驟 1 中的結果集。請從下表檢查最適合您案例的原因。

案例常見原因

意外建立的統計數據自動建立統計數據的延遲

統計數據建立在 5 分鐘後失敗自動建立統計數據逾時

案例	常見原因
意外建立的統計數據	自動建立統計數據的延遲
統計數據建立在 5 分鐘後失敗	自動建立統計數據逾時

已封鎖：編譯並行

並行編譯區塊很少發生。不過，如果您遇到這種類型的區塊，表示在短時間內提交大量查詢，並已排入佇列以開始編譯。

風險降低

減少同時提交的查詢數目。

已封鎖：資源配置

因為資源配置遭到封鎖，表示您的查詢正在等候根據下列項目執行：

根據與使用者相關聯的資源類別或工作負載群組指派授與的記憶體數量。
系統或工作負載群組上的可用記憶體數量。
(選擇性) 工作負載群組/分類器重要性。

風險降低

等候封鎖會話完成。
評估資源類別選擇。如需詳細資訊，請參閱並行限制。
評估是否偏好終止封鎖會話。

複雜查詢或較舊的 JOIN 語法

您可能會遇到預設查詢優化器方法經證實無效的情況，因為編譯階段需要很長的時間。如果查詢如下，就可能發生此情況：

牽涉到大量的聯結和/或子查詢， (複雜的查詢) 。
使用子句中的 FROM 聯結器 (而非 ANSI-92 樣式聯結) 。

雖然這些案例並不常見，但您可以選擇嘗試覆寫預設行為，以減少查詢優化器選擇計劃所需的時間。

風險降低

使用 ANSI-92 樣式聯結。
新增查詢提示： OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))。如需詳細資訊，請參閱 FORCE ORDER 和基數估計 (SQL Server) 。
將查詢分成多個較不複雜的步驟。

長時間執行的DROP TABLE或TRUNCATE TABLE

為了提高運行時間效率， DROP TABLE 和 TRUNCATE TABLE 語句會將記憶體清除延後至背景進程。不過，如果您的工作負載在短時間內執行大量 DROP/TRUNCATE TABLE 語句，元數據可能會變得很密集，並導致後續 DROP/TRUNCATE TABLE 語句執行速度變慢。

風險降低

識別維護期間、停止所有工作負載，並執行 DBCC SHRINKDATABASE ，以強制立即清除先前卸除或截斷的數據表。

狀況不良的CCI (通常)

叢集數據行存放區索引 (CCI) 健康情況需要額外的元數據，這可能會導致查詢優化器花更多時間來判斷最佳計劃。若要避免這種情況，請確定所有CCI都處於良好健康狀態。

風險降低

評估並更正專用 SQL 集區中的叢集數據行存放區索引健康情況。

自動建立統計數據的延遲

自動建立統計數據選項默ON認為，AUTO_CREATE_STATISTICS可協助確保查詢優化器能夠做出良好的分散式計劃決策。不過，自動建立程式本身可能會讓初始查詢花費的時間比後續執行的查詢還要長。

風險降低

如果第一次執行查詢時一致地需要建立統計數據，您必須在執行查詢之前手動建立統計數據。

自動建立統計數據逾時

自動建立統計數據選項默ON認為，AUTO_CREATE_STATISTICS可協助確保查詢優化器能夠做出良好的分散式計劃決策。自動建立統計數據是為了回應 SELECT 語句而發生，而且有 5 分鐘的閾值可完成。如果數據大小和/或要建立的統計數據數目需要超過 5 分鐘的閾值，將會放棄自動建立統計數據，讓查詢可以繼續執行。無法建立統計數據可能會對查詢優化器產生有效率分散式執行計劃的能力造成負面影響，導致查詢效能不佳。

風險降低

手動建立統計數據，而不是依賴已識別數據表/數據行的自動建立功能。

執行階段問題

使用下表來分析步驟 2 中的結果集。判斷您的案例，並檢查常見原因以取得詳細資訊和可能的風險降低步驟。

案例	常見原因
`EstimatedRowCount`/`ActualRowCount`< 25%	不正確的估計值
值 `Description` 表示 `BroadcastMoveOperation` ，而查詢會參考複寫的數據表。	未快取的復寫數據表
1. `@ShowActiveOnly` = 0 2.觀察到 () `step_index` 的步驟數目偏高或非預期。 3.數據表之間的聯結器數據行數據類型不相同。	不相符的數據類型/大小
1.值 `Description` 表示 `HadoopBroadcastOperation`、 `HadoopRoundRobinOperation` 或 `HadoopShuffleOperation`。 2.指定`total_elapsed_timestep_index`的值在執行之間不一致。	臨機操作外部數據表查詢

檢查在total_elapsed_time步驟 3 中取得的值。如果在指定步驟的幾個發行版中明顯較高，請遵循下列步驟：
1. 針對每個數據表執行下列命令，以檢查欄位step_id中TSQL所參考之每個數據表的數據分佈，
```
DBCC PDW_SHOWSPACEUSED(<table>);
```
2. 如果 <最小數據列值>/<最大數據列值>> 0.1，請移至儲存) 的數據扭曲 (。
3. 否則，請移至進行中數據扭曲。

不正確的估計值

讓您的統計數據保持最新狀態，以確保查詢優化器會產生最佳計劃。當估計的數據列計數明顯小於實際計數時，必須維護統計數據。

風險降低

建立/更新統計數據。

未快取的復寫數據表

如果您已建立複寫數據表，而且無法適當地準備複寫的數據表快取，則非預期的效能會因為額外的數據移動或建立次佳的分散式計劃而導致效能不佳。

風險降低

在 DML 作業之後，讓復寫的快取變暖。
如果有頻繁的 DML 作業，請將資料表的散發變更為 ROUND_ROBIN。

不相符的數據類型/大小

聯結數據表時，請確定聯結數據行的數據類型和大小相符。否則，會導致不必要的數據移動，而降低 CPU、IO 和網路流量對工作負載其餘部分的可用性。

風險降低

重建數據表以更正沒有相同數據類型和大小的相關數據表數據行。

臨機操作外部數據表查詢

針對外部數據表的查詢的設計目的是要將數據大量載入專用SQL集區。針對外部數據表的臨機操作查詢可能會因為外部因素而發生變動的持續時間，例如並行記憶體容器活動。

風險降低

先將數據載入專用 SQL 集區，然後查詢載入的數據。

儲存) 的數據扭曲 (

數據扭曲表示數據不會平均分散到散發。分散式計劃的每個步驟都需要在移至下一個步驟之前完成所有散發套件。當您的資料扭曲時，無法達到 CPU 和 IO 等處理資源的完整潛力，進而導致運行時間變慢。

風險降低

檢閱分散式數據表的指引，以協助您選擇更適當的散發數據行。

進行中數據扭曲

進行中數據扭曲是儲存) 問題 (數據扭曲的變體。但這不是磁碟上數據的分佈扭曲。特定篩選或群組數據的分散式計劃本質會造成 ShuffleMoveOperation 類型作業。此作業會產生扭曲的輸出以供下游取用。

風險降低

請確定統計數據已建立且為最新狀態。
變更數據 GROUP BY 行的順序，以具有較高的基數數據行作為潛在客戶。
如果聯結涵蓋多個數據行，請建立多重數據行統計數據。
將查詢提示 OPTION(FORCE_ORDER) 新增至您的查詢。
重構查詢。

等候類型問題

如果上述任何常見問題都不適用於您的查詢，步驟 3 數據會提供機會來判斷 (wait_type 和 wait_time) 中哪些等候類型會干擾執行時間最長的步驟的查詢處理。有大量的等候類型，而且由於類似的緩和措施，它們會分組為相關的類別。請遵循下列步驟來找出查詢步驟的等候類別：

wait_type識別步驟 3 中花費最多時間的。
在等候類別對應數據表中找出等候類型，並識別其中包含的等候類別。
展開下列清單中與等候類別相關的區段，以取得建議的風險降低措施。

編譯

請遵循下列步驟來減輕編譯類別的等候類型問題：

針對涉及有問題查詢的所有物件重建索引。
更新與有問題查詢有關之所有物件的統計數據。
再次測試有問題的查詢，以驗證問題是否持續發生。

如果問題持續發生，則：

使用 下列專案建立.sql 檔案：

SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;

開啟 [命令提示字元] 視窗，然後執行下列命令：

sqlcmd −S <servername>.database.windows.net −d <databasename> −U <username> −G −I −i .\<sql_file_name>.sql −y0 −o .\<output_file_name>.txt

在 <文字編輯器> 中開啟output_file_name.txt。找出散發層級執行計劃並複製， (從步驟 2 中所識別的最長執行步驟) 開頭<ShowPlanXML>的行，貼到擴展名為 .sqlplan 的個別文本檔中。

注意： 分散式計劃的每個步驟通常會記錄 60 個散發層級執行計劃。請確定您正在準備及比較來自相同分散式計劃步驟的執行計劃。
步驟 3 查詢經常會顯示一些比其他散發版花費更長的時間。在 SQL Server Management Studio 中，將 (從) 長時間執行散發所建立的 .sqlplan 檔案的散發層級執行計劃，與快速執行的散發進行比較，以分析差異的潛在原因。

鎖定，背景工作線程

請考慮變更經常進行小型變更的數據表，以利用數據列存放區索引，而不是CCI。
批處理變更，並以較不頻繁的數據列更新目標。

緩衝區 IO、其他磁碟 IO、Tran 記錄 IO

狀況不良的CCI

狀況不良的CCI會增加IO、CPU和記憶體配置，進而對查詢效能造成負面影響。若要減輕此問題，請嘗試下列其中一種方法：

評估並更正專用 SQL 集區中的叢集數據行存放區索引健康情況。
執行並檢閱優化叢集數據行存放區索引中所列查詢的輸出，以取得基準。
請遵循重建索引的步驟來改善區段品質，並以範例問題查詢中所涉及的數據表為目標。

過期的統計數據

過期的統計數據可能會導致產生未優化的分散式計劃，其涉及的數據移動超出所需的數目。不必要的數據移動不僅會增加待用數據上的工作負載，也會增加上的工作負載 tempdb。因為 IO 是跨所有查詢的共享資源，所以整個工作負載可能會感受到效能影響。

若要解決這種情況，請確定所有統計數據都是最新的，而且已備妥維護計劃，讓使用者工作負載的統計數據保持更新。

繁重的 IO 工作負載

您的整體工作負載可能會讀取大量數據。 Synapse 專用 SQL 集區會根據 DWU 調整資源。若要達到更好的效能，請考慮下列任一或兩者：

針對查詢使用較大的資源類別。
增加計算資源。

CPU、平行處理原則

案例	緩和措施
CCI 健康情況不佳	評估和更正專用 SQL 集區中的叢集數據行存放區索引健康情況
使用者查詢包含轉換	將所有格式設定和其他轉換邏輯移至 ETL 進程，以便儲存格式化的版本
工作負載的優先順序不正確	實作工作負載隔離
工作負載的 DWU 不足	考慮增加計算資源

網路IO

如果在步驟 2 的作業期間RETURN發生問題，

減少並行平行處理程序的數目。
將受影響最大的程式相應放大到另一個用戶端。

對於所有其他數據移動作業而言，網路問題可能是專用 SQL 集區內部的問題。若要嘗試快速減輕此問題，請遵循下列步驟：

將專用 SQL 集區調整為 DW100c
調整回您想要的 DWU 層級

SQL CLR

若要避免經常使用函式， FORMAT() 請實作替代方式來轉換數據 (例如， CONVERT() 使用樣式) 。

共用方式為

針對專用 SQL 集區上的慢速查詢進行疑難解答

步驟 1：識別request_id (也称为 QID)

步驟 2：判斷查詢花費的時間

步驟 3：檢閱步驟詳細數據

步驟 4：診斷和緩解

編譯階段問題

執行階段問題

等候類型問題

意見反應

意見反應

其他資源