Optimized plan forcing with Query Store
Applies to: SQL Server 2022 (16.x) Preview
Query optimization is a multi-phased process of generating a "good-enough" query execution plan. In some cases, query compilation, a part of query optimization, can represent a large percentage of overall query execution time and consume significant system resources. Optimized plan forcing is part of the intelligent query processing family of features. Optimized plan forcing reduces compilation overhead for repeating forced queries. Once the query execution plan is generated, specific compilation steps are stored for reuse as an optimization replay script. An optimization replay script is stored as part of the compressed showplan XML in Query Store, in a hidden
Optimized plan forcing implementation
When a query first goes through the compilation process, a threshold based on estimation of the time spent in optimization (based on the query optimizer input tree) will determine whether an optimization replay script is created. This threshold is not documented.
After compilation completes, several runtime metrics become available to assess whether the previous estimation was correct. If it's confirmed the threshold was crossed, the optimization replay script is eligible for persistence. These runtime metrics include the number of objects accessed, the number of joins, the number of optimization tasks executed during optimization, and the actual optimization time.
The potential benefit of using an optimization replay script is also compared to the overhead of storing the optimization replay script. An estimation of the relative time to replay the optimization replay script is compared with the time that was spent executing the normal optimization process, based on the number of optimization tasks stored in optimization replay script and the number of optimization tasks executed during normal compilation. If replaying the optimization replay script shows substantial benefit in reducing compilation time, the optimization replay script is persisted.
When the optimized plan forcing feature is enabled, the eligibility criteria for optimized plan forcing is:
- Only query plans that go through full optimization are eligible, which can be verified by the presence of the
- Statements with RECOMPILE hint and distributed queries are not eligible.
However, if the Query Store independently captures a query plan that was scoped out by optimized plan forcing, the optimization replay script will be created for a second recompilation of that same query, subject to default recompilation events. Learn more about recompilation in Recompiling Execution Plans.
Even if an optimization replay script was generated, it might not be persisted in the Query Store if the Query Store configured capture policies criteria isn't met, notably the number of executions of that statement and its cumulated compile and execution times. In this case, the invalid optimization replay script will be removed from memory asynchronously.
Enable and disable optimized plan forcing
You can enable or disable optimized plan forcing for a database. When optimized plan forcing is enabled for a database, you may disable it for individual queries using the
DISABLE_OPTIMIZED_PLAN_FORCING query hint. You may also disable optimized plan forcing for a query plan which is forced in Query Store.
Enable or disable optimized plan forcing for a database
Optimized plan forcing is enabled by default for new databases created in SQL Server 2022 and higher. The Query Store must be enabled for every database where optimized plan forcing is used. Upgraded instances with existing databases or databases restored from a lower version of SQL Server will not have optimized plan forcing enabled by default.
To enable optimized plan forcing at the database level, use the
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON database scoped configuration. You must enable Query Store if it isn't already enabled. Find example code in Example A, or learn more about Query Store in Monitor performance by using the Query Store.
To disable optimized plan forcing at the database level, use the
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF database scoped configuration.
Disable optimized plan forcing with a query hint
When the optimized plan forcing feature is enabled in a database, you can disable optimized plan forcing for an individual query by using the
DISABLE_OPTIMIZED_PLAN_FORCING query hint.
Find an example of applying this query hint in Example E.
Force a plan with Query Store, but disable optimized plan forcing
The sp_query_store_force_plan procedure includes a
disable_optimized_plan_forcing parameter, with default OFF (0) (if the parameter is omitted from the procedure call).
sys.query_store_plan catalog view includes columns that indicate if the plan has an associated optimization replay script, and adds a new state to existing failure reason column specific to associated optimization replay script. Learn more in sys.query_store_plan (Transact-SQL).
A. Enable Query Store and optimized plan forcing for a database
The following code enables Query Store on a database, then enables optimized plan forcing on the database. Learn more about options enabling Query Store in ALTER DATABASE SET options (Transact-SQL).
Before running the code, connect to the appropriate user database.
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. Select all queries that have an optimization replay script
The following example code selects all query_ids that have an optimization replay script in Query Store. Connect to the appropriate user database before running the example code.
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. Force a plan and disable optimized plan forcing in Query Store
The following code forces a plan in Query Store, but disables optimized plan forcing. Before running the following code, replace
@plan_id with a combination appropriate for your instance.
EXEC sp_query_store_force_plan @query_id=148, @plan_id=4, @disable_optimized_plan_forcing=1; GO
Learn more in sp_query_store_force_plan (Transact-SQL).
D. Select all queries where optimized plan forcing is disabled by Query Store
The following example queries all plans that have been forced in Query Store where
is_optimized_plan_forcing_disabled has been set to
1. Before running the code, connect to the appropriate user database.
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 for a query
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (DISABLE_OPTIMIZED_PLAN_FORCING); GO
Learn more about Query Store and optimized plan forcing in the following articles:
Submit and view feedback for