Edit

Share via

Optimized plan forcing with Query Store

  • Article

Applies to: SQL Server 2022 (16.x) Azure SQL Database

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 and requires the Query Store to be enabled and in "read write" mode. 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 OptimizationReplay attribute.

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) determines whether an optimization replay script is created.

After compilation completes, several runtime metrics become available to assess whether the previous estimation was correct. If the Database Engine confirms 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. This estimate is 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.

Considerations

When the optimized plan forcing feature is enabled, the eligibility criteria for optimized plan forcing is:

  1. Only query plans that go through full optimization are eligible, which can be verified by the presence of the StatementOptmLevel="FULL" property.

  2. Statements with RECOMPILE hint and distributed queries aren't eligible.

However, if the Query Store independently captures a query plan that was scoped out by optimized plan forcing, the optimization replay script is 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 aren'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 is 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 can disable it for individual queries using the DISABLE_OPTIMIZED_PLAN_FORCING query hint. You can 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 (16.x) 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, 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. In order to use this parameter, an extra parameter is required by the sp_query_store_force_plan stored procedure. The extra parameter is called @replica_group_id. By default, the primary @replica_group_id has a value of one (1) even in the case where there are no configured secondary replicas.

Find an example of applying the appropriate parameters to the sp_query_store_force_plan stored procedure in Example C.

The 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.

Examples

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

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.

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 @query_id and @plan_id with a combination appropriate for your instance. The sp_query_store_force_plan stored procedure expects that the @replica_group_id parameter is passed in as the third parameter value when attempting to disabled optimized plan forcing in Query Store. This can be used to disable optimized plan forcing for a particular forced plan on a specific replica. A value of @replica_group_id = 1 is used to disable the feature on the primary replica.

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

Learn more in sp_query_store_force_plan.

D. Select all queries where optimized plan forcing is disabled by Query Store

The following example queries all plans that were forced in Query Store where is_optimized_plan_forcing_disabled is 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

The following example disables optimized plan forcing for a query using the DISABLE_OPTIMIZED_PLAN_FORCING query hint.

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

Related content