gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertDeze browser wordt niet meer ondersteund.
Upgrade naar Microsoft Edge om te profiteren van de nieuwste functies, beveiligingsupdates en technische ondersteuning.
Applies to:
SQL Server 2022 (16.x)
Azure SQL Database
SQL database in Microsoft Fabric
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.
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.
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 StatementOptmLevel="FULL"
property.
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.
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.
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.
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.
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.
The 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.
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
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
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.
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
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
gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertTraining
Module
Optimalisatie van queryprestaties verkennen - Training
Optimalisatie van queryprestaties verkennen
Certificering
Microsoft Certified: Azure Database Administrator Associate - Certifications
Beheer een SQL Server-databaseinfrastructuur voor cloud-, on-premises en hybride relationele databases met behulp van de relationele Microsoft PaaS-databaseaanbiedingen.
Documentatie
sp_query_store_force_plan (Transact-SQL) - SQL Server
Enables forcing a particular plan for a particular query in the Query Store.
Query Store hints - SQL Server
Learn about the Query Store hints feature, which can be used to shape query plans without changing application code.
Monitor performance by using the Query Store - SQL Server
Query Store provides insight on query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. Query Store captures history of queries, plans, and runtime statistics.