針對專用 SQL 集區的緩慢查詢進行疑難排解

發行項
11/20/2024

適用於：Azure Synapse Analytics

本文可協助您找出原因，並針對 Azure Synapse Analytics 專用 SQL 集區上查詢的常見效能問題套用防護功能。

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

注意

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

在 Azure Data Studio 中開啟筆記本

重要

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

過時的統計數據
狀況不良的叢集資料行存放區索引（CCIS）

若要節省疑難解答時間，請確定已建立統計數據，且已重建最新狀態和CCIS。

步驟 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：診斷和減輕

編譯階段問題

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

描述常見原因

Compilation Concurrency 封鎖：編譯並行

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

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

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

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

針對在慢速查詢提交之後立即執行的一或多個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 以強制立即清除先前卸載或截斷的數據表。

狀況不良的 CCIS （一般）

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

風險降低

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

自動建立統計數據的延遲

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

風險降低

如果第一次執行查詢需要建立統計數據，您必須先手動建立統計數據，才能執行查詢。

自動建立統計數據逾時

自動建立統計數據選項AUTO_CREATE_STATISTICSON預設會協助確保查詢優化器可以做出良好的分散式計劃決策。統計數據的自動建立會在回應 SELECT 語句時發生，而且有 5 分鐘的閾值可完成。如果要建立的數據和/或統計數據數目超過 5 分鐘閾值，則會放棄自動建立統計數據，讓查詢可以繼續執行。建立統計數據的失敗可能會對查詢優化器產生有效率的分散式執行計劃的能力產生負面影響，導致查詢效能不佳。

風險降低

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

執行階段問題

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

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

total_elapsed_time檢查步驟 3 中取得的值。如果在指定步驟的幾個散發中明顯較高，請遵循下列步驟：
1. 針對每個資料表執行下列命令，檢查欄位中step_id所參考TSQL之每個資料表的數據散發：
```
DBCC PDW_SHOWSPACEUSED(<table>);
```
2. 如果<最小數據列值/<最大數據列值>>> 0.1，請移至 [數據扭曲] （stored）。
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 Log IO

狀況不良的CCIS

狀況不良的 CCIS 有助於增加 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：診斷和減輕

編譯階段問題

執行階段問題

等候類型問題

意見反應

其他資源