sp_query_store_force_plan
procedure.Database Engine in SQL Server 2017 (14.x) provides information about regressed plans and recommended corrective actions.Additionally, Database Engine enables you to fully automate this process and let Database Engine fix any problem found relatedto the plan changes.sp_query_store_force_plan
procedure. The best practice would be to force the last known good plan because older plans might be invalid due to statistic or index changes. The user who forces the last known good plan should monitor performance of the query that is executed using the forced plan and verify that forced plan works as expected. Depending on the results of monitoring and analysis, plan should be forced or user should find some other way to optimize the query.Manually forced plans should not be forced forever, because the Database Engine should be able to apply optimal plans. The user or DBA should eventuallyunforce the plan using sp_query_store_unforce_plan
procedure, and let the Database Engine find the optimal plan.type | description | datetime | score | details | ... |
---|---|---|---|---|---|
FORCE_LAST_GOOD_PLAN | CPU time changed from 4 ms to 14 ms | 3/17/2017 | 83 | queryId recommendedPlanId regressedPlanId T-SQL | |
FORCE_LAST_GOOD_PLAN | CPU time changed from 37 ms to 84 ms | 3/16/2017 | 26 | queryId recommendedPlanId regressedPlanId T-SQL |
FORCE_LAST_GOOD_PLAN
reason | score | script | query_id | current plan_id | recommended plan_id | estimated_gain | error_prone |
---|---|---|---|---|---|---|---|
CPU time changed from 3 ms to 46 ms | 36 | EXEC sp_query_store_force_plan 12, 17; | 12 | 28 | 17 | 11.59 | 0 |
estimated_gain
represents the estimated number of seconds that would be saved if the recommended plan would be executed instead of the current plan. The recommended plan should be forced instead of the current plan if the gain is greater than 10 seconds. If there are more errors (for example, time-outs or aborted executions) in the current plan than in the recommended plan, the column error_prone
would be set to the value YES
. Error prone plan is another reason why the recommended plan should be forced instead of the current one.