Query Store – Force Plan – Part 5

Maintaining Query Performance Stability

For queries that are executed multiple times you may notice that SQL Server used different plans which resulted in different resource utilization and duration. With Query Store you can easily detect when the query performance regressed and determine the optimal plan within a period of interest. Then you can force that optimal plan for future query execution.

You can also identify inconsistent query performance for a query with parameters (either auto- parameterized or manually parameterized). Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

Force or a plan for a query (apply forcing policy). When a plan is forced for a certain query, every time a query comes to execution it will be executed with the plan that is forced.

 

image

select the query plan and click on Force Query Plan , it will popup a window asking the confirmation

image

Once you confirm to use the plan as force plan ,SQL Server will always use the same plan for execution

 

image

 

You can force the plan using T-SQL

use databasename
go
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;
go

 

When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

Remove plan forcing for a query. To rely again on the SQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

use databasename
go
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49
go

Leave a Reply

Your email address will not be published. Required fields are marked *