In this article, we will explore the concept of Optimized Plan Forcing in SQL Server and how it can be used to improve performance. Query performance can be affected by various factors, such as changes in statistics, schema modifications, or index creation/deletion. Resolving performance regressions caused by changes in execution plans can be time-consuming and challenging.

Query Store is a database feature in SQL Server that collects query performance and execution statistics. It provides insights into query plan changes over time, making it easier to monitor and troubleshoot performance issues. Query Store captures the history of queries, plans, and runtime statistics, allowing you to identify plan deviations and regressions quickly.

One of the features of Query Store is Optimized Plan Forcing, which allows you to force the optimizer to use a specific query plan for a particular query. This can be useful when you know that a specific plan is the best choice for a particular request. By “locking” a plan in the Query Store, you can ensure that the optimizer uses that plan when generating an execution plan for the query.

To enable Optimized Plan Forcing, you simply need to set the Query Store option to enable it. However, it is important to note that enabling this feature for all queries or ad-hoc queries can have adverse effects on system performance. It is recommended to selectively use plan optimization for specific queries when the optimal plan is known in advance.

Let’s take a look at an example to understand how Optimized Plan Forcing works:

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

In the above example, we query the Query Store to retrieve the details of query IDs and their respective query plans. We can see that query ID 1 has two plans, plan ID 1 and plan ID 3. The “is_forced_plan” column has a value of 0, indicating that no plan is currently forced for this query.

To force a specific plan for a query, we can use the following command:

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

After forcing the plan, we can query the Query Store again to see the updated results:

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

In the updated results, we can see that for query ID 1, the “is_forced_plan” column is now 1, indicating that the plan has been forced. The “plan_forcing_type_desc” column shows that the plan was manually forced.

It is important to note that while Optimized Plan Forcing can improve the performance of certain queries, it should be used selectively and tested before implementation. Enabling this feature for all queries or ad-hoc queries without proper evaluation can have unintended consequences.

In conclusion, Optimized Plan Forcing is a powerful feature in SQL Server that allows you to force the use of a specific query plan for improved performance. By selectively using plan optimization and testing before implementation, you can significantly enhance query performance and make your queries faster and more efficient.