In this article, we will delve into the world of Query Store and explore how to use Optimized Plan Forcing to improve performance in SQL Server 2022. We will discuss what it is, how it works, and how it can impact your system's performance.

The Query Store

SQL Server execution plans for any given query typically change over time for a variety of reasons, including changes in statistics, schema changes, index creation or deletion, and so on. Due to memory constraints, plans also leave the plan cache. Resolving query performance regressions brought on by changes to the execution plan can be difficult and time-consuming as a result.

Query Store is a database feature in SQL Server that collects query performance and execution statistics for each query in the database. The Query Store provides insight into query plan changes over time, making it easier to monitor and troubleshoot query performance issues. Plan deviations and regressions can be quickly identified using Query Store.

Query Store simplifies performance troubleshooting by enabling you to quickly discover performance differences caused by query plan changes. Query Store automatically captures the history of queries, plans, and runtime statistics and saves them for review. It partitions the data by timeframes so you can see database usage patterns and understand when query plan changes occur on the server. Additionally, the data is persistent, unlike the plan cache, which clears information when the server reboots or restarts. Query Store allows you to customize how much and how long Query Store can store data.

Wait stats are another source of useful information for troubleshooting the database engine. Starting with SQL Server 2017 (14.x) and Azure SQL Database, Query Store includes a dimension that tracks wait statistics.

The Query Store contains three stores:

• a plan store for persisting the execution plan information.
• a runtime stats store for persisting the execution statistics information.
• a wait stats store for persisting wait statistics information.

Optimized Plan Forcing

Optimized plan forcing is a new capability in SQL Server 2022 intended to reduce the time it takes to compile certain queries if the query plan is forced. Some queries by their nature can take a significant amount of time to compile, Optimized plan forcing is a feature of SQL Server that allows you to force the optimizer to use a specific query plan for a specific query.

This is useful when there are situations when a particular plan is known to be the best choice for a particular request. In such cases, applying an optimized plan can help force the use of that plan. Sometimes because of outdated statistics, the optimizer will choose the wrong execution plan. If you know the right execution plan, you can force SQL Server to use that plan.

Essentially, forcing an optimized plan works by "locking" a particular plan in the Query Store so that the optimizer will use that plan when generating an execution plan for a particular query. With this feature, you can create plan guide that instruct the database engine to use a specific query plan each time a query is executed.

Enabling Optimized Plan Forcing is easy. All you need to do is set the Query Store option to enable it. However, do not enable this feature for all or ad-hoc queries. This can lead to plan changes that can adversely affect system performance.

An Example

I will run below query to query store to get the details of query id and respective query plans I am running this on my test database StackOverflow2013.

SELECT eqs.query_id,  qsp.is_forced_plan,qsp.plan_forcing_type_desc,
eqs.query_hash, qsp.query_plan_hash, eqs.last_compile_batch_sql_handle,
qsp.query_id, qsp.plan_id
FROM sys.query_store_query eqs INNER JOIN sys.query_store_plan qsp
ON eqs.query_id = qsp.query_id

You can see this in SSMS below.

Here you can see that query id 1 has 2 plans, Plan_id 1 and plan_id 3. Also the above output has 0 value in Is_forced_plan column. Now we will force this query to use plan id 3. We will do that by running below command.

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 3;

Now we again query to the query store using our same previous script.

SELECT  eqs.query_id,  qsp.is_forced_plan,qsp.plan_forcing_type_desc,
eqs.query_hash, qsp.query_plan_hash, eqs.last_compile_batch_sql_handle,
qsp.query_id, qsp.plan_id
FROM sys.query_store_query eqs INNER JOIN sys.query_store_plan qsp
ON eqs.query_id = qsp.query_id
order by eqs.last_execution_time desc

Note the results below. Here you can see that for query_id 1, now column  is_force_plan  is 1 and plan_forcing_type_desc is MANUAL  as we have manually forced this plan on query.

With Optimized Plan Forcing, you may be able to improve the performance of certain queries. However, enabling this feature for all queries or ad-hoc queries may not always be the right choice. It is essential to test and evaluate before enabling this feature.

Conclusion

Optimized Plan forcing is great feature if we know the optimized plan is known in advance. we need to keep few things in mind while using this feature.

• Plan selectively - Selectively use plan optimization for specific queries when the optimal plan is known in advance.
• Test before implementing - Before enabling optimized plan enforcement for a specific query, test and evaluate the performance impact.
• Improve Query Performance - Query Store and Optimized Plan Forcing can significantly improve query performance, making queries faster and more efficient.