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

發行項
05/04/2023

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

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

最佳化計畫強制實作

查詢第一次完成編譯流程時，根據最佳化時間估計的閾值 (基於查詢最佳化工具輸入樹狀) 會決定是否建立最佳化重新執行指令碼。

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

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

考量

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

只有經 StatementOptmLevel="FULL" 屬性驗證的最佳化查詢計劃符合資格。
包含 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)。

範例

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

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

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

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 參數會傳入作為第三個參數值。此參數可在特定複本上特定的強制執行計畫，停用最佳化計畫強制。值為 1 - @replica_group_id=1 會用來停用主要複本上的功能。

EXEC 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 (Transact-SQL)。

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查詢提示，停用查詢的最佳化計畫強制。此範例使用 AdventureWorks 範例資料庫。

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

下一步

在下列文章中深入了解查詢存放區與最佳化計畫強制：