SQL Server 和 Azure SQL 受控執行個體中可偵測的查詢效能瓶頸類型

發行項
03/19/2024

在嘗試解決效能瓶頸時，請先判斷瓶頸是在查詢處於執行中狀態還是等候狀態時發生的。隨著這項判斷的結果，應採用不同的解決方法。本文將討論與每種問題類型相關的問題和解決方法。

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

執行相關的問題：執行相關的問題通常與編譯問題 (會導致查詢計劃欠佳) 或執行問題 (與資源不足或過度使用有關) 有關。 等候相關問題：等候相關問題通常與下列項目相關：

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

本文介紹 SQL Server 和 Azure SQL 受控執行個體，另請參閱 Azure SQL 資料庫中可偵測的查詢效能瓶頸類型。

編譯問題導致次佳查詢計畫

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

使用 DMV 中的遺漏索引和查詢執行計畫，識別任何遺漏的索引。本文說明如何使用遺漏的索引要求來偵測及微調非叢集索引。
嘗試更新統計資料或重建索引，以取得更好的計畫。啟用資料庫的自動計畫修正，以自動減輕這些問題。
在進階疑難排解步驟中，使用查詢存放區提示來使用查詢存放區套用查詢提示，而不需變更程式碼。
嘗試變更資料庫相容性層級，並實作智慧型查詢處理。 SQL 查詢最佳化工具可能會根據資料庫的相容性層級產生不同的查詢計畫。更高的相容性層級提供更智慧型的查詢處理功能。

此微調應用程式和資料庫效能文章中的查詢微調和提示範例會顯示由於參數化查詢導致查詢計畫的影響、如何偵測此條件，以及如何使用查詢提示來解析解決此問題。

如需查詢處理的詳細資訊，請參閱查詢處理架構指南。
若要變更資料庫相容性層級，並深入了解相容性層級之間的差異，請參閱 ALTER DATABASE。
若要深入了解基數估計，請參閱基數估計

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

下列各節將討論如何解決使用次佳查詢執行計畫之查詢的問題。

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

查詢最佳化工具產生的查詢執行計畫僅針對特定參數值 (或一組值) 最佳化，而快取計畫對連續執行中使用的參數值並非最佳時，就會發生參數敏感性計畫 (PSP) 問題。然後，次佳方案可能會導致查詢效能問題，並降低整體工作負載輸送量。

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

有數個因應措施可以減輕 PSP 問題。每個因應措施都有相關聯的優缺點：

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

若要套用查詢提示，請修改查詢或使用查詢存放提示來套用提示，而不需變更程式碼。在 SQL Server 2022 之前的 SQL Server 版本中，使用計劃指南。

如需解決 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 提示，將不會快取計畫。

重新編譯 (或快取收回之後的全新編譯) 仍會導致產生與原始計畫相同的查詢執行計畫。計畫從先前或原始計畫變更時，很可能會有以下解釋：

已變更實體設計：例如，新建立的索引更有效率地涵蓋查詢的需求。如果查詢最佳化工具決定使用該新索引比使用原本為第一個查詢執行版本選取的資料結構更理想，就可能會將新索引用於新編譯。參考物件的任何實體變更都可能導致在編譯時間選擇新計畫。
伺服器資源差異：某個系統中的計畫與另一個系統中的計畫不同時，資源可用性 (例如可用的處理器數目) 可能會影響產生的計畫。例如，如果一個系統有多個處理器，可能會選擇平行計畫。如需平行處理的詳細資訊，請參閱設定 max degree of parallelism 伺服器組態選項。
不同的統計資料：與參考物件相關聯的統計資料可能已變更，或可能與原始系統的統計資料不同。如果發生統計資料變更和重新編譯，查詢最佳化工具會使用從變更時開始的統計資料。修訂後的統計資料之資料分佈和頻率可能會與原始編譯的資料分佈和頻率不同。這些變更可用來建立基數估計值。（基數估計值是預期流經邏輯查詢樹狀結構的資料列數目)。基數估計值的變更可能會導致您選擇不同的實體運算子和相關聯的作業順序。即使是統計資料的次要變更，也可能導致查詢執行計畫變更。
已變更資料庫相容性層級或基數估算器版本：資料庫相容性層級的變更會啟用可能導致不同查詢執行計畫的新策略和功能。除了資料庫相容性層級之外，停用或啟用的追蹤旗標 4199 或資料庫範圍設定的變更狀態 QUERY_OPTIMIZER_HOTFIXES 也會影響編譯時間的查詢執行計畫選擇。追蹤旗標 9481 (強制舊版 CE) 和 2312 (強制預設 CE) 也會影響計畫。

Azure SQL 受控執行個體中的資源限制問題

與次佳查詢計畫和遺漏索引無關的查詢效能緩慢，通常與資源不足或過度使用有關。如果查詢計畫是最佳，查詢 (和資料庫) 可能會達到受控執行個體的資源限制。例如，服務等級的記錄寫入輸送量可能過多。

sys.dm_db_resource_stats DMV 會傳回資料庫的 CPU、I/O 和記憶體使用量。即使資料庫中沒有任何活動，每隔 15 秒也會產生一個資料列。歷程記錄資料會保留一小時。
sys.server_resource_stats DMV 會傳回 Azure SQL 受控執行個體的 CPU 使用率和儲存體資料。此資料是在 15 秒鐘間隔內收集及彙總。
許多累積耗用大量 CPU 的個別查詢

如果您識別問題為資源不足，則可以升級資源，以增加資料庫的容量來吸收 CPU 需求。如需調整受控執行個體的相關資訊，請參閱服務層級資源限制

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

應用程式流量和工作負載數量增加可能會導致 CPU 使用量增加。但您必須小心，才能正確診斷此問題。看到高 CPU 使用率問題時，請回答這些問題，以判斷增加是否由工作負載磁碟區變更所造成：

來自應用程式的查詢是否會導致高 CPU 使用率問題？
針對您可以識別的最上層 CPU 取用查詢：
- 有多個執行計畫與相同的查詢相關聯嗎？若是如此，為什麼？
- 對於具有相同執行計畫的查詢，執行時間是一致的嗎？執行計數是否增加？如果是，工作負載增加可能是導致效能問題的原因。

總之，如果查詢執行計畫未以不同方式執行，但 CPU 使用量隨著執行計數增加而增加，效能問題可能與工作負載增加有關。

識別導致 CPU 問題的工作負載數量變更未必很容易。考量下列因素：

已變更的資源使用量：例如，假設 CPU 使用量在延長期間增加到 80%。單單 CPU 使用量並不表示工作負載磁碟區已變更。即使應用程式執行相同的工作負載，查詢執行計畫中的迴歸和資料分佈變更也會導致更多的資源使用量。
新查詢的外觀：應用程式可能會在不同的時間驅動一組新的查詢。
要求數目增加或減少：此案例是工作負載最明顯的度量。查詢數目並非一律對應更高的資源使用率。不過，假設其他因素保持不變，此計量仍是重大訊號。
平行處理：然而，過度的平行處理原則可能會佔用其他查詢的 CPU 和背景工作執行緒資源，進一步導致其他同時工作負載效能惡化。如需平行處理的詳細資訊，請參閱設定 max degree of parallelism 伺服器組態選項。

一旦您排除了次佳計畫和與執行問題相關的等候相關問題，效能問題通常是查詢可能會等待某些資源。等候相關問題可能由下列原因所造成：

封鎖：

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

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

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

如果工作負載沒有足夠的記憶體，頁面的預期壽命可能會下降，或者查詢可能會無法獲得所需的記憶體。在某些情況下，查詢最佳化工具中的內建智慧會修正記憶體相關問題。請參閱使用 DMV 找出記憶體授與問題。如果您發生記憶體不足的錯誤，請檢閱 sys.dm_os_out_of_memory_events。也請考慮 Azure SQL 受控執行個體硬體的記憶體最佳化進階系列層，其記憶體與虛擬核心的比例較高。

顯示最高等候類別的方法

這些方法通常用來顯示最高類別的等候類型：

使用查詢存放區尋找一段時間後每個查詢的等候統計資料。在查詢存放區中，等候類型會合併到等候類別。您可以在 sys.query_store_wait_stats (Transact-SQL) 中找到等候類別至等候類型的對應。
使用 sys.dm_os_wait_stats 傳回查詢作業期間執行之執行緒所遇到之所有等候的相關資訊。您可以使用此彙總檢視來診斷 Azure SQL 受控執行個體或 SQL Server 執行個體的效能問題。查詢可以是等候資源、佇列等候或外部等候。
使用 sys.dm_os_waiting_tasks 傳回有關正在等候某項資源的工作佇列資訊。

在高 CPU 案例中，如果出現下列情況，查詢存放區和等候統計資料可能不會反映 CPU 使用量：

高 CPU 使用量查詢仍在執行中。
容錯移轉發生時，高 CPU 使用量查詢正在執行。

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

提示

其他工具：

共用方式為