使用查詢存放區監視工作負載的最佳做法

文章
07/19/2024

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

本文概述工作負載與 SQL Server 查詢存放區搭配使用的最佳做法。

如需設定和管理查詢存放區的詳細資訊，請參閱使用查詢存放區監視效能。
如需使用查詢存放區探索可採取動作的資訊與調整效能的詳細資訊，請參閱使用查詢存放區調整效能。
如需操作 Azure SQL Database 中查詢存放區的相關資訊，請參閱操作 Azure SQL Database 中的查詢存放區。
Azure Synapse Analytics 預設不會針對專用 SQL 集區啟用查詢存放區，但可以加以啟用。不支援查詢存放區的進一步設定選項。如需詳細資訊，請參閱 Azure Synapse Analytics 中的歷程查詢儲存體和分析。

使用最新版 SQL Server Management Studio

SQL Server Management Studio 提供一組使用者介面，其設計為用來設定查詢存放區，以及用來取用所收集與工作負載相關的資料。下載最新版的 SQL Server Management Studio。

如需如何在疑難排解案例中使用查詢存放區的快速描述，請參閱 Query Store Azure blogs。

在 Azure SQL 資料庫中使用查詢效能深入解析

如果您在 Azure SQL Database 中執行查詢存放區，則可使用查詢效能深入解析來分析一段時間內的資源耗用量。雖然可使用 Management Studio 和 Azure Data Studio 來取得所有查詢的資源耗用量詳細資訊 (例如 CPU、記憶體和 I/O)，但查詢效能深入解析能提供一個快速並有效率方式來判斷資源耗用量對資料庫整體 DTU 耗用量的影響。如需詳細資訊，請參閱 Azure SQL 資料庫查詢效能深入解析。

搭配彈性集區資料庫使用查詢存放區

您可以放心地在所有資料庫中使用查詢存放區，即使在緊密壓縮的 Azure SQL Database 彈性集區中也是一樣。我們已妥善解決在彈性集區中啟用大量資料庫的查詢存放區時，以前可能發生的所有過度使用資源問題。

開始針對查詢效能進行疑難排解

查詢存放區的疑難排解工作流程很簡單，如下圖所示：

查詢存放區疑難排解圖表：啟用查詢存放區、讓查詢存放區收集資料、找出並修正有問題的查詢。

如上節所述，使用 Management Studio 啟用查詢存放區，或執行下列 Transact-SQL 陳述式：

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

為了讓查詢存放區收集到可準確呈現您工作負載的資料集，這需要一些時間。通常一天就已足夠，即使是非常複雜的工作負載。不過，在您啟用功能之後，即可開始探索資料並識別需要立即處理的查詢。在 Management Studio 的 [物件總管] 中，前往資料庫節點下的查詢存放區子資料夾，以開啟特定案例的疑難排解檢視。

Management Studio 查詢存放區檢視會操作執行計量組，每個都以下列任一統計資料函式表示：

SQL Server 版本	執行計量	統計資料函式
SQL Server 2016 (13.x)	CPU 時間、持續時間、執行計數、邏輯讀取、邏輯寫入、記憶體耗用量、實體讀取、CLR 時間、平行處理原則的程度 (DOP) 和資料列計數	平均值、最大值、最小值、標準差、總計
SQL Server 2017 (14.x)	CPU 時間、持續時間、執行計數、邏輯讀取、邏輯寫入、記憶體耗用量、實體讀取、CLR 時間、平行處理原則的程度、資料列計數、記錄檔記憶體、TempDB 記憶體和等候時間	平均值、最大值、最小值、標準差、總計

下圖顯示顯示如何找出查詢存放區檢視︰

SSMS 的螢幕擷取畫面，其中顯示查詢存放區檢視的位置。

下表說明每個查詢存放區檢視的使用時機：

SQL Server Management Studio 檢視	狀況
迴歸查詢	找出執行計量最近已迴歸的查詢 (例如，變得更糟)。使用此檢視，以將您在應用程式中觀察到需要修正或改善的效能問題與實際查詢相互關聯。
整體資源耗用量	針對任何執行計量，分析資料庫的整體資源耗用量。使用此檢視來識別資源模式 (每日與每晚的工作負載) 和最佳化資料庫的整體耗用量。
最耗用資源的查詢	選擇一個您感興趣的執行計量，並識別在所供時間間隔中具有最極端值的查詢。使用此檢視以將注意力放在最相關的查詢，也就是對資料庫資源耗用量影響最大的查詢。
強制計劃的查詢	使用查詢存放區列出先前的強制計畫。使用此檢視快速存取所有目前的強制計畫。
高變化的查詢	當具有高執行變化的查詢與任何可用維度 (例如，所需時間間隔的持續時間、CPU 時間、IO 和記憶體使用量) 產生關聯時，對其進行分析。您可以使用此檢視來識別含廣泛變化效能的查詢，這種效能會跨應用程式影響使用者體驗。
查詢等候統計資料	分析資料庫中最常使用的等候類別，以及哪些查詢最常參與所選取的等候類別。使用此檢視來分析等候統計資料，並識別可能跨應用程式影響使用者體驗的查詢。適用於：從 SQL Server Management Studio v18.0 和 SQL Server 2017 (14.x) 起的版本。
追蹤查詢	即時追蹤最重要的查詢的執行。一般而言，當您有包含強制計畫的查詢且想要確定查詢效能是否穩定時，會使用此檢視。

提示

如需深入瞭解如何使用 Management Studio 識別最耗用資源的查詢，並修正那些因為計劃選擇變更而迴歸的查詢，請參閱Query Store Azure Blogs。

當您識別出效能次佳的查詢時，您的動作將取決於問題本質。

如果查詢執行時有多個計劃，且最後一個計劃明顯比前一個計劃差，則您可以使用計劃強制機制來強制執行。 SQL Server 會嘗試在最佳化工具中強制執行計劃。如果計劃強制失敗，會引發 XEvent，系統會指示最佳化工具以一般方式最佳化。

查詢存放區強制計畫按鈕的 SSMS 的螢幕擷取畫面。

注意

上一個圖形針對特定查詢計劃可能具有不同形狀，每個可能狀態的意義如下：

形狀	意義
Circle	查詢已完成，亦即已順利完成一般執行。
Square	已取消，亦即用戶端起始項目已中止執行。
Triangle	已失敗，亦即例外狀況已中止執行。

此外，該形狀大小會反映指定時間間隔內的查詢執行計數。此大小會隨著執行數目提高而增加。

您可能會推斷查詢遺漏最佳執行所需的索引。此資訊會顯示於查詢執行計畫內。建立遺漏的索引，並使用查詢存放區來檢查查詢效能。

查詢存放區計畫的 SSMS 螢幕擷取畫面，其中已醒目提示遺漏索引通知。

如果您在 SQL Database 上執行您的工作負載，請註冊 SQL Database 索引建議程式以自動接收索引建議。

在某些情況下，如果您發現執行計劃中預估和實際資料列數目之間的差異非常大，您可以強制統計資料重新編譯。
請重寫有問題的查詢，以充分利用查詢參數化或實作較佳的邏輯等。

提示

在 Azure SQL Database 中，請考慮使用查詢存放區提示功能，在查詢上強制查詢提示，而不需變更程式碼。如需詳細資訊和範例，請參閱查詢存放區提示。

驗證查詢存放區會持續收集查詢資料

查詢存放區可以無訊息方式變更作業模式。請定期監視查詢存放區的狀態，以確定查詢存放區持續運作，並採取動作以免因可預防的問題導致失敗。執行下列查詢來判斷作業模式，並檢視最相關的參數：

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

actual_state_desc 和 desired_state_desc 之間的差異表示作業模式自動發生變更。最常見的變更就是查詢存放區以無訊息模式切換到唯讀模式。在極少數情況下，查詢存放區會因為內部錯誤而造成 ERROR 狀態。

當實際狀態是唯讀時，請使用 readonly_reason 資料行來判斷根本原因。通常您會發現查詢存放區因為已超過大小配額而轉換為唯讀模式。在此情況下，readonly_reason 是設定為 65536。針對其他原因，請參閱 sys.database_query_store_options (Transact-SQL)。

請考慮下列步驟將查詢存放區切換為讀寫模式並啟用資料收集：

使用 ALTER DATABASE 的 MAX_STORAGE_SIZE_MB 選項增加最大儲存體大小。

使用下列陳述式清除查詢存放區資料：

ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;

您可以執行下列陳述式，套用其中一或兩個步驟，明確地將作業模式變更回讀寫：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

採取下列積極步驟：

您可以套用最佳作法，以避免無訊息的操作模式變更。確認查詢存放區大小一律低於允許的最大值，以大幅降低轉換為唯讀模式的機率。啟用以大小為基礎的原則，如設定查詢存放區一節所述，以讓查詢存放區在接近大小限制時自動清除資料。
若要確認保留最新的資料，請設定以時間為基礎的原則，以定期移除過時的資訊。
最後，請考慮將 [查詢擷取模式] 設定為 [自動]，因為這會篩選掉和工作負載較不相關的查詢。

ERROR 狀態

若要復原查詢存放區，請嘗試明確地設定讀寫模式，並再次檢查實際狀態。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

如果問題持續發生，表示磁碟上保存了損毀的查詢存放區資料。

從 SQL Server 2017 (14.x) 開始，您可以透過在受影響的資料庫中執行 sys.sp_query_store_consistency_check 預存程序來復原查詢存放區。您必須先停用查詢存放區，才能嘗試進行復原作業。以下是可以使用或修改以完成 QDS 一致性檢查和復原的範例查詢：

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

針對 SQL Server 2016 (13.x)，您需要從查詢存放區清除資料，如下所示。

若復原失敗，您可以嘗試清除查詢存放區，再設定讀寫模式。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

請避免使用非參數化查詢

在非必要時使用非參數化查詢並不是最佳做法。隨選分析就是一個範例。您無法重複使用快取的計劃，這會強制查詢最佳化工具編譯每個唯一查詢文字的查詢。如需詳細資訊，請參閱使用強制參數化的指導方針。

此外，查詢存放區可能因為大量潛在不同的查詢文字，導致出現大量具有類似圖形的不同執行計劃，而快速超過大小配額。因此，您的工作負載會出現次佳效能，而查詢存放區可能會切換到唯讀模式，或不斷刪除資料以嘗試跟上內送查詢。

請考慮下列選項：

在適用情況下將查詢參數化。例如，將查詢包裝在預存程序或 sp_executesql 內。如需詳細資訊，請參閱參數和執行計劃的重複使用。
如果您的工作負載包含許多單次使用隨選批次與不同查詢計劃，請使用針對隨選工作負載最佳化選項。
- 比較不同 query_hash 值的數目與 sys.query_store_query 中項目總數。如果比例接近 1，您的隨選工作負載會產生不同查詢。
如果不同的查詢計劃數目不大，請針對資料庫或查詢子集套用強制參數化。
- 使用計劃指南，僅針對選取的查詢強制參數化。
- 如果您的工作負載中有少數不同查詢計劃，請使用參數化資料庫選項命令來設定強制參數化。例如，當不同 query_hash 計數和 sys.query_store_query 中項目總數之間比例遠小於 1 的情況。
將 QUERY_CAPTURE_MODE 設定為 AUTO，以自動篩選掉資源耗用量少的隨選查詢。

提示

使用 Entity Framework (EF) 等物件關聯式對應 (ORM) 解決方案時，手動 LINQ 查詢樹狀結構或特定原始 SQL 查詢等應用程式查詢可能不會進行參數化，這會影響計畫的重複使用以及追蹤查詢存放區中查詢的能力。如需詳細資訊，請參閱 EF 查詢快取和參數化和 EF 原始 SQL 查詢。

在查詢存放區中尋找非參數化查詢

您可以使用下列查詢、使用查詢存放區 DMV、在 SQL Server、Azure SQL 受控執行個體或 Azure SQL Database 中，找到儲存在查詢存放區中的計劃數目：

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

以下範例會建立擴充事件工作階段來擷取事件query_store_db_diagnostics，這在診斷查詢資源耗用量時十分實用。在 SQL Server 中，此擴充事件工作階段預設會在 SQL Server 記錄資料夾中建立事件檔案。例如，在 Windows 的預設 SQL Server 2019 (15.x) 安裝中，應於資料夾 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 中建立事件檔案 (.xel 檔案)。針對 Azure SQL 受控執行個體，請改為指定 Azure Blob 儲存體位置。如需詳細資訊，請參閱 Azure SQL 受控執行個體的 XEvent event_file。 Azure SQL Database 無法使用事件 ‘qds.query_store_db_diagnostics'。

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

使用此資料，您也可以在查詢存放區中找到計劃計數，以及許多其他統計資料。尋找事件資料中的 plan_count、query_count、max_stmt_hash_map_size_kb 和 max_size_mb 資料行，以瞭解查詢存放區所追蹤的記憶體數量和計劃數目。如果計劃計數高於一般，則可能表示非參數化查詢增加。使用下列查詢存放區 DMV 查詢來檢閱查詢存放區中的參數化查詢和非參數化查詢。

針對參數化查詢：

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

針對非參數化查詢：

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

避免對包含物件使用 DROP 和 CREATE 模式

查詢存放區會將查詢項目與包含物件 (例如預存程序、函式和觸發程序) 建立關聯。當您重新建立一個包含物件時，系統會針對相同查詢文字產生新的查詢項目。這會讓您無法追蹤該查詢一段時間的效能統計資料，也無法使用計劃強制機制。若要避免這種情況，請盡量使用 ALTER <object> 程序變更包含物件的定義。

定期檢查強制計劃的狀態

強制執行計畫是一個針對重要查詢修正效能的方便的機制，並讓查詢變得更容易預測。然而，因為有計劃提示與計劃指南，所以並不保證會在未來的執行中使用強制執行計劃。一般而言，當執行計劃所參考的物件遭改變或卸除，導致資料庫結構描述跟著變更時，強制執行計劃就會開始失敗。在此情況下，SQL Server 會退而重新編譯查詢，而實際的強制執行失敗原因會顯示在 sys.query_store_plan 中。下列查詢會傳回強制執行計畫的相關資訊︰

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

如需完整的原因清單，請參閱 sys.query_store_plan。您也可以使用 query_store_plan_forcing_failed XEvent 追蹤強制執行計畫失敗及針對此問題進行疑難排解。

提示

在 Azure SQL Database 中，請考慮使用查詢存放區提示功能，在查詢上強制查詢提示，而不需變更程式碼。如需詳細資訊和範例，請參閱查詢存放區提示。

如果您有強制計劃的查詢，請避免重新命名資料庫

執行計劃會使用三部分名稱 (如 database.schema.object) 來參考物件。

如果您重新命名資料庫，強制執行計劃即會失敗，而導致重新編譯所有後續查詢執行。

在任務關鍵性伺服器中使用查詢存放區

您可以使用查詢存放區和全域追蹤旗標 7745 與 7752 來提升資料庫可用性。如需詳細資訊，請參閱追蹤旗標。

追蹤旗標 7745 會防止下列預設行為：查詢存放區將資料寫入磁碟之後，才能關閉 SQL Server。這表示已收集但尚未保存到磁碟的查詢存放區資料將會遺失 (取決於 DATA_FLUSH_INTERVAL_SECONDS 所定義的時間範圍)。
追蹤旗標 7752 提供非同步載入查詢存放區的功能。這可讓資料庫成為上線狀態，並在查詢存放區完全復原之前執行查詢。預設行為是執行查詢存放區的同步載入。這個預設行為使得查詢一定會在查詢存放區復原之後執行，但同時也防止資料收集過程遺漏任何查詢。

注意

從 SQL Server 2019 (15.x) 開始，此行為由引擎控制，追蹤旗標 7752 沒有任何作用。

重要

若您要將查詢存放區用於 SQL Server 2016 (13.x) 中的 Just-In-Time 工作負載深入解析，請盡快擬定計劃，安裝 SQL Server 2016 (13.x) SP2 CU2 (KB 4340759) 中的效能延展性改進功能。若沒有這些改進功能，當資料庫承受繁重的工作負載時，可能會發生執行緒同步鎖定競爭，伺服器效能可能會變慢。尤其是，您可能會在 QUERY_STORE_ASYNC_PERSIST 執行緒同步鎖定或 SPL_QUERY_STORE_STATS_COOKIE_CACHE 執行緒同步鎖定時發現嚴重的爭用。套用此改進功能後，查詢存放區將不會再造成執行緒同步鎖定競爭。

重要

如果您在 SQL Server (SQL Server 2016 (13.x) 至 SQL Server 2017 (14.x) 的版本) 中使用查詢存放區來取得 Just-In-Time 工作負載深入解析，請儘快規劃在 2016 SQL Server 2016 (13.x) SP2 CU15、SQL Server 2017 (14.x) CU23 和 SQL Server 2019 (15.x) CU9 中安裝效能延展性改進功能，。若沒有這項改進功能，當資料庫承受繁重的臨機操作工作負載時，查詢存放區可能會使用大量的記憶體，而伺服器效能可能會變慢。套用這項改進功能後，查詢存放區將會對其各種元件可使用的記憶體數量施加內部限制，並且可在資料庫引擎重獲足夠的記憶體之前，自動將作業模式變更為唯讀。查詢存放區內部記憶體限制有可能變更，因此並未載於文件中。

使用 Azure SQL Database 主動式異地複寫的查詢存放區

Azure SQL Database 次要主動式異地複寫其查詢存放區會是主要複本活動的唯讀複本。

避免與 Azure SQL Database 異地複寫不相符的階層。次要資料庫的計算大小應等於或接近主資料庫，且和主資料庫位於相同的服務層。在 sys.dm_db_wait_stats 中尋找 HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO 等候類型，這會指出受次要延遲影響的主要複本交易記錄速率節流。

如需預估和設定次要 Azure SQL 資料庫主動式異地複寫大小的詳細資訊，請參閱設定次要資料庫。

針對工作負載調整查詢存放區

如要深入探討查詢存放區設定和管理的最佳做法與相關建議，請參閱管理查詢存放區的最佳做法這篇文章。

分享方式：

使用查詢存放區監視工作負載的最佳做法

使用最新版 SQL Server Management Studio

在 Azure SQL 資料庫中使用查詢效能深入解析

搭配彈性集區資料庫使用查詢存放區

開始針對查詢效能進行疑難排解

驗證查詢存放區會持續收集查詢資料

ERROR 狀態

請避免使用非參數化查詢

在查詢存放區中尋找非參數化查詢

避免對包含物件使用 DROP 和 CREATE 模式

定期檢查強制計劃的狀態

如果您有強制計劃的查詢，請避免重新命名資料庫

在任務關鍵性伺服器中使用查詢存放區

使用 Azure SQL Database 主動式異地複寫的查詢存放區

針對工作負載調整查詢存放區

意見反映

更多資源

分享方式：

使用查詢存放區監視工作負載的最佳做法

使用最新版 SQL Server Management Studio

在 Azure SQL 資料庫中使用查詢效能深入解析

搭配彈性集區資料庫使用查詢存放區

開始針對查詢效能進行疑難排解

驗證查詢存放區會持續收集查詢資料

ERROR 狀態

請避免使用非參數化查詢

在查詢存放區中尋找非參數化查詢

避免對包含物件使用 DROP 和 CREATE 模式

定期檢查強制計劃的狀態

如果您有強制計劃的查詢，請避免重新命名資料庫

在任務關鍵性伺服器中使用查詢存放區

使用 Azure SQL Database 主動式異地複寫的查詢存放區

針對工作負載調整查詢存放區

相關內容

意見反映

更多資源