Azure SQL Database 中可偵測的查詢效能瓶頸類型

適用于:Azure SQL資料庫

在嘗試解決效能瓶頸時,請先判斷瓶頸是在查詢處於執行中狀態還是等候狀態時發生的。 隨著這項判斷的結果,應採用不同的解決方法。 使用下圖可協助您了解可能導致執行或等候相關問題的因素。 本文將討論每個問題類型及其相關的解決方式。

您可以使用 Intelligent Insights 或 SQL Server DMV 來偵測這些類型的效能瓶頸。

工作負載狀態

執行的相關問題:執行的相關問題通常與編譯問題有關,這些問題會導致查詢計劃欠佳,或與資源不足或過度使用相關的執行問題。 等候相關問題:等候相關的問題通常與下列因素有關:

  • 鎖定 (封鎖)
  • I/O
  • tempdb 使用方式相關的爭用
  • 記憶體授與等候

本文是關於 Azure SQL Database,另請參閱Azure SQL 受控執行個體 中可偵測的查詢效能瓶頸類型

編譯問題導致查詢計劃欠佳

SQL 查詢最佳化工具所產生的欠佳計劃,可能是查詢效能變慢的原因。 SQL 的查詢最佳化工具可能會產生欠佳的計畫,原因包括遺漏索引、過時的統計資料、要處理的資料列數目不正確,或所需記憶體的估計不準確。 如果您知道查詢在過去或另一個資料庫上執行得更快,請比較實際執行計畫,以查看它們是否不同。

使用次佳的查詢執行計畫解析查詢

下列各節將討論如何使用最佳的查詢執行計劃來解析查詢。

具有參數敏感性計劃 (PSP) 問題的查詢

參數敏感性計劃 (PSP) 問題是指查詢最佳化工具產生僅適用於特定參數值 (或一組值) 的查詢執行計劃,且快取計劃也不適用於連續執行中使用的參數值。 不是最佳的方案將會導致查詢效能問題,並降低整體工作負載輸送量。

如需參數探測和查詢處理的詳細資訊,請參閱查詢處理架構指南

有幾個因應措施可以緩解 PSP 問題。 每個因應措施都有相對的取捨和缺點:

  • 2022 SQL Server 2022 (16.x) 引進的新功能是「參數敏感性計畫」優化,其會嘗試降低參數敏感度所造成的大部分次佳查詢計劃。 這會在 Azure SQL Database 中使用資料庫相容性層級 160 來啟用。
  • 在每次查詢執行時使用 RECOMPILE 查詢提示。 此因應措施會交易編譯時間和增加的 CPU,以獲得更好的計劃品質。 針對需要高輸送量的工作負載,通常不適用 RECOMPILE 選項。
  • 使用 OPTION (OPTIMIZE FOR…) 查詢提示,以典型參數值覆寫實際參數值,可為大多數參數值的可能性提供足夠完善的計劃。 此選項需要充分了解最佳的參數值和相關聯的計畫特性。
  • 使用 OPTION (OPTIMIZE FOR UNKNOWN) 查詢提示來覆寫實際參數值,而不使用密度向量的平均值。 您也可以將傳入的參數值擷取到區域變數中,然後使用述詞內的區域變數,而不使用參數本身。 針對此修正,平均密度必須「足夠良好」。
  • 透過使用 DISABLE_PARAMETER_SNIFFING 查詢提示完全停用參數探查。
  • 使用 KEEPFIXEDPLAN 查詢提示,以避免快取中的重新編譯。 此因應措施假設快取中的計劃就是足夠良好的常見計劃。 您也可以停用自動統計資料更新,以避免良好計劃遭到收回,並編譯新的不佳計劃。
  • 藉由重寫查詢並在查詢文字中加入提示,以明確地使用 USE PLAN 查詢提示來強制執行計劃。 或者,使用查詢存放區或啟用自動調整來設定特定計劃。
  • 使用程序的的巢狀集合取代單一程序,每個程序都可以根據條件式邏輯和相關聯的參數值來使用。
  • 建立靜態程序定義的動態字串執行替代方案。

若要套用查詢提示,請修改查詢或使用查詢存放區提示來套用提示,而不進行程式碼變更。

如需解決 PSP 問題的詳細資訊,請參閱下列部落格文章:

因為不當參數化所造成的編譯活動

當查詢包含常值時,資料庫引擎會自動將陳述式參數化,或由使用者明確地將陳述式參數化以減少編譯數目。 若查詢有大量編譯使用相同模式但不同的常值,則可能會導致高 CPU 使用率。 同樣地,如果您僅將查詢部分參數化以繼續使用常值,則資料庫引擎不會進一步將查詢參數化。

以下是部分參數化查詢的範例:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

在此範例中,t1.c1 會採用 @p1,但 t2.c2 繼續接受 GUID 作為常值。 在這種情況下,如果變更 c2 的值,查詢會被視為不同的查詢,並將進行新的編譯。 為了減少此範例中的編譯,您也需要將 GUID 參數化。

下列查詢顯示依查詢雜湊的查詢計數,以確定查詢是否已正確參數化:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

影響查詢計劃變更的因素

查詢執行計劃的重新編譯,可能會產生與原始快取計劃不同的查詢計劃。 原始計劃可能會因各種原因而自動重新編譯:

  • 查詢會參考架構中的變更
  • 查詢會參考資料表的資料變更
  • 查詢內容選項已變更

已編譯的計劃可能會因為各種原因而從快取中退出,例如:

  • 執行個體重新啟動
  • 資料庫範圍的設定變更
  • 記憶體壓力
  • 清除快取的明確要求

如果您使用 RECOMPILE 提示,則計劃不會進入快取。

重新編譯 (或在快取收回之後從頭編譯) 仍可能導致產生與原始相同的查詢執行計劃。 當計劃是變更自先前或原始方案時,可能會有下列說明:

  • 已變更的實體設計:例如,新建立的索引可更有效地涵蓋查詢的需求。 如果使用新索引比原先針對第一版查詢執行所選取的資料結構更理想,則查詢最佳化工具可能會決定在新編譯中使用新的索引。 參考物件的任何實體變更可能會使編譯時間有新的計劃選項。

  • 伺服器資源差異:當某個系統中的計劃與另一個系統中的計劃不同時,資源可用性 (例如可用的處理器數目) 可能會影響所產生的計劃。 例如,如果一個系統有更多處理器,可能會選擇平行計劃。 如需 Azure SQL Database 平行處理原則的詳細資訊,請參閱在 Azure SQL Database 中設定平行處理原則的最大程度 (MAXDOP)

  • 不同的統計資料:參考物件相關聯的統計資料可能已變更,或與原始系統的統計資料不同。 如果統計資料變更,且發生重新編譯,則查詢最佳化工具會使用從變更時開始的統計資料。 已修訂統計資料的資料分佈和頻率可能與原始編譯版本不同。 這些變更會用來建立基數估計值。 ([基數估計值] 是預期流經邏輯查詢樹狀結構的資料列數目。) 基數估計值的變更可能會導致您選擇不同的實體運算子和相關聯的運算順序。 即使是統計資料的次要變更,仍可能會導致查詢執行計劃變更。

  • 變更資料庫相容性層級或基數估算器版本:對資料庫相容性層級所做的變更,將會啟用可能產生不同查詢執行計劃的新策略和功能。 除了資料庫相容性層級以外,已停用或已啟用的追蹤旗標 4199,或資料庫範圍設定的變更狀態 QUERY_OPTIMIZER_HOTFIXES 也可能會影響編譯時間的查詢執行計劃選擇。 追蹤旗標 9481 (強制舊版 CE) 和 2312 (強制預設 CE) 也會影響此計劃。

資源限制問題

查詢效能緩慢若與欠佳的查詢計劃和遺漏索引無關,則通常與資源不足或過度使用有關。 如果查詢計劃是最佳的,查詢 (和資料庫) 可能會達到資料庫或彈性集區的資源限制。 範例可能會超出服務層級的記錄寫入輸送量。

如果您確認問題為資源不足,則可以升級資源以增加資料庫的容量,藉此滿足 CPU 需求。 如需調整 Azure SQL 資料庫的詳細資訊,請參閱 在 Azure SQL Database 中調整單一資料庫資源在 Azure SQL Database 中調整彈性集區

工作負載量提高所造成的效能問題

應用程式流量和工作負載量的增加會造成 CPU 使用量增加。 但是您必須小心地診斷此問題。 發現高 CPU 使用量的問題時,請您回答這些問題,以判斷此增加是否為工作負載量的變更所造成:

  • 來自應用程式的查詢是否為高 CPU 問題的原因?

  • 針對您可識別 CPU 使用率最多的查詢

    • 是否有多個執行計畫關聯至相同的查詢? 若是如此,為什麼?
    • 針對具有相同執行計畫的查詢,其執行時間是否一致? 執行計數是否有增加? 如果情況如上所述,工作負載的增加可能會導致效能問題。

總結來說,如果查詢執行計劃沒有以不同的方式執行,但 CPU 使用率隨著執行計數而增加,則效能問題可能與工作負載增加有關。

要找出導致 CPU 問題的工作負載量變更,有時會遇到一些困難。 請考慮下列因素:

  • 變更的資源使用量:例如,假設 CPU 使用率在很長一段時間內增加到 80% 的案例。 CPU 使用率本身並不表示工作負載量已變更。 即使應用程式正在執行相同的工作負載,查詢執行計劃的迴歸和資料散發的變更也可能導致資源使用量增加。

  • 新查詢的外觀:應用程式可能會在不同的時間驅動一組新的查詢。

  • 要求數目增加或減少:這個狀況是最明顯的工作負載量值。 查詢數目不一定會與更多的資源使用率相對應。 然而,假設其他因素不變,此計量仍然是重要的訊號。

使用 Intelligent Insights 來偵測工作負載增加計畫回歸

在消除了與執行問題相關的欠佳計劃和等候相關問題,效能問題通常是查詢可能正在等候某些資源。 等候相關的問題可能是由下列原因所造成:

  • 封鎖

    一個查詢可能會鎖定資料庫中的物件,而其他查詢則可能嘗試存取相同的物件。 您可以使用 DMVIntelligent Insights 來識別封鎖查詢。 如需詳細資訊,請參閱瞭解並解決Azure SQL資料庫封鎖問題

  • IO 問題

    查詢可能正在等待頁面寫入資料或記錄檔。 在此情況下,請檢查 DMV 中的 INSTANCE_LOG_RATE_GOVERNORWRITE_LOGPAGEIOLATCH_* 等候統計資料。 請參閱使用 DMV 來識別 IO 效能問題

  • Tempdb 問題

    如果工作負載使用暫存資料表,或計劃中有 tempdb 溢寫,則查詢可能存在 tempdb 輸送量的問題。 若要進一步調查,請檢閱找出 tempdb 問題

  • 記憶體相關問題

    如果工作負載沒有足夠的記憶體,頁面的預期壽命可能會下降,或者查詢可能會無法獲得所需的記憶體。 在某些情況下,查詢最佳化工具中的內建智慧會修正記憶體相關問題。 請參閱使用 DMV 找出記憶體授與問題。 如需詳細資訊和範例查詢,請參閱針對 Azure SQL Database 中的記憶體不足錯誤進行疑難排解。 如果您發生記憶體不足的錯誤,請檢閱 sys.dm_os_out_of_memory_events

顯示最上層等候類別的方法

這些方法通常用來顯示等候類型最上層的類別:

  • 使用 Intelligent Insights 來識別因為等待時間增加而效能降低的查詢
  • 使用查詢存放區來尋找一段時間內每個查詢的等候統計資料。 在查詢存放區中,等候類型會合併到等候類別中。 您可以在 sys.query_store_wait_stats 找到等候類別與等候類型的對應。
  • 使用 sys.dm_db_wait_stats 傳回在查詢作業期間,執行緒執行時所遇到的所有等候相關資訊。 您可以使用此彙總的檢視來診斷關於 Azure SQL Database 及特定查詢和批次的效能問題。 查詢可能正在等候資源、佇列等候或外部等候。
  • 使用 sys.dm_os_waiting_tasks 傳回正在等候某些資源的工作佇列相關資訊。

在高 CPU 使用率的案例中,查詢存放區和等候統計資料在下列情況可能不會反映 CPU 使用率:

  • 高 CPU 耗用量的查詢仍在執行。
  • 發生容錯移轉時,高 CPU 耗用的查詢正在執行。

追蹤查詢存放區和等候統計資料的 DMV 只會顯示已成功完成和逾時查詢的結果。 DMV 不會顯示目前執行的陳述式的資料,需要等到完成之後才會顯示。 使用動態管理檢視 sys.dm_exec_requests 追蹤目前正在執行的查詢和相關聯的背景工作時間。

後續步驟