共用方式為

使用查詢存放區的強制最佳化計畫

  • 發行項

適用於： SQL Server 2022 (16.x) Azure SQL Database

查詢最佳化是產生「良好」查詢執行計畫的多階段流程。 在部分案例中，查詢編譯是查詢最佳化的一部分，可以表示高比例的整體查詢執行時間，並取用大量的系統資源。 最佳化計畫強制是智慧查詢處理功能系列的一部分。 最佳化計畫強制會減少重複強制查詢的編譯額外負荷，並要求以「讀取寫入」模式啟用查詢存放區。 查詢執行計畫產生後會儲存特定的編譯步驟，並作為最佳化重新執行指令碼重複使用。 最佳化重新執行指令碼會作為壓縮的執行程序表 XML 的一部分，以隱藏 OptimizationReplay 屬性，儲存在查詢存放區。

最佳化計畫強制實作

當查詢第一次通過編譯程式時，根據優化所花費時間的估計臨界值（根據查詢優化器輸入樹狀結構）決定是否建立優化重新執行腳本。

完成編譯後，數個執行階段計量即可用來評估之前的估計是否正確。 如果 資料庫引擎 確認已超過閾值，則優化重新執行腳本符合持續性的資格。 這些執行階段計量包括存取的物件數目、聯結數目、最佳化期間執行的最佳化工作數目，及實際的最佳化時間。

使用最佳化重新執行指令碼的潛在優點是，媲美儲存最佳化重新執行指令碼的額外負荷。 相較於執行一般優化程式所花費的時間，估計重新執行優化腳本的相對時間。 此估計值是根據儲存在優化重新執行腳本中的優化工作數目，以及一般編譯期間執行的優化工作數目。 如果重新執行最佳化重新執行指令碼在減少編譯時間方面顯示顯著的優點，即保存最佳化重新執行指令碼。

考量

啟用最佳化計畫強制功能時，最佳化計畫強制的適用性準則：

  1. 只有經 StatementOptmLevel="FULL" 屬性驗證的最佳化查詢計劃符合資格。

  2. 具有 RECOMPILE 提示和分散式查詢的語句不符合資格。

不過，如果 查詢存放區 獨立擷取由優化計劃強制限定範圍的查詢計劃，則會針對該相同查詢的第二次重新編譯建立優化重新執行腳本，但受限於預設的重新編譯事件。 深入了解重新編譯執行計畫的重新編譯。

即使產生優化重新執行腳本，如果不符合 查詢存放區 設定的擷取原則準則，它可能不會保存在 查詢存放區 中，特別是該語句的執行次數及其匯總的編譯和執行時間。 在此情況下，會以異步方式從記憶體中移除無效的優化重新執行腳本。

啟用和停用最佳化計畫強制

您可以啟用或停用資料庫的最佳化計畫強制。 針對資料庫啟用優化計劃強制時，您可以使用查詢提示針對個別查詢 DISABLE_OPTIMIZED_PLAN_FORCING 停用它。 您也可以針對在 查詢存放區 中強制的查詢計劃停用優化計劃強制。

啟用或停用資料庫的最佳化計畫強制

SQL Server 2022 (16.x) 和更新版本中，建立的新資料庫會預設啟用最佳化計畫強制。 使用最佳化計畫強制的每個資料庫都必須啟用查詢存放區。 使用現有資料庫的升級實例，或從較低版本的 SQL Server 還原的資料庫，預設會強制啟用優化計劃。

若要在資料庫層級啟用最佳化計畫強制，請使用 ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON 資料庫範圍設定。 請務必啟用查詢存放區 (如果未啟用)。 在範例 A 中尋找範例程式碼，或在使用查詢存放區監視效能中，深入了解查詢存放區。

若要在資料庫層級停用最佳化計畫強制，請使用 ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF 資料庫範圍設定。

使用查詢提示停用最佳化計畫強制

在資料庫中啟用最佳化計畫強制功能後，您可以使用 DISABLE_OPTIMIZED_PLAN_FORCING 查詢提示，停用個別查詢的最佳化計畫強制。

在範例 E 中，尋找套用此查詢提示的範例。

強制執行查詢存放區的計畫，但停用最佳化計畫強制

sp_query_store_force_plan 程序包含 disable_optimized_plan_forcing 參數。 若要使用此參數，預存程式需要 sp_query_store_force_plan 額外的參數。 額外的參數稱為 @replica_group_id。 根據預設，即使沒有設定的次要複本，主要 @replica_group_id 複本也有一個 （1） 的值。

在範例 C 中尋找將適當參數套用至sp_query_store_force_plan預存程式的範例。

sys.query_store_plan 目錄檢視包含資料行，指出計畫是否關聯最佳化重新執行指令碼，並新增狀態至關聯的最佳化重新執行指令碼特定的現有失敗原因資料行。 深入瞭解 sys.query_store_plan。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 啟用資料庫的查詢存放區與最佳化計畫強制

下列程式碼會啟用資料庫的查詢存放區，然後啟用資料庫的最佳化計畫強制。 深入瞭解在 ALTER DATABASE SET 選項中啟用 查詢存放區 的選項。

請連線適當的使用者資料庫，再執行程式碼。

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. 選取包含最佳化重新執行指令碼的所有查詢

下列範例程式碼會選取查詢存放區中，包含最佳化重新執行指令碼的所有 query_ids。 請連線適當的使用者資料庫，再執行範例程式碼。

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. 強制執行查詢存放區的計畫並停用最佳化計畫強制

下列程式碼會強制執行查詢存放區的計畫，但停用最佳化計畫強制。 執行下列程式碼前，請以適用於執行個體的組合，取代 @query_id 與 @plan_id。 預sp_query_store_force_plan存程式預期@replica_group_id在嘗試停用 查詢存放區 中強制優化計劃時，參數會以第三個參數值的形式傳入。 此參數可在特定複本上特定的強制執行計畫，停用最佳化計畫強制。 的值 @replica_group_id = 1 是用來停用主要複本上的功能。

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

深入瞭解 sp_query_store_force_plan。

D. 選取查詢存放區停用最佳化計畫強制的所有查詢

下列範例會查詢在 查詢存放區 is_optimized_plan_forcing_disabled 1中強制設定為 的所有計劃。 請連線適當的使用者資料庫，再執行程式碼。

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. 停用查詢的最佳化計畫強制

下列範例使用 DISABLE_OPTIMIZED_PLAN_FORCING 查詢提示，停用查詢的最佳化計畫強制。

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

相關內容