SQL Server creates execution plans that are used to execute T-SQL statements. SQL Server can use different plans to execute the query, where the choice of a plan depends on data statistics, available indexes, etc. The plan for a T-SQL query would be changed over time. Sometime, SQL Server can choose a plan that is worse than the previous plans and this is known as a plan change regression.

In this case, you will notice that T-SQL query is starting to execute much slower, and you would need to fix this problem. Since SQL Server 2016, there are a lot of tools that can help you to fix this issue. In this article, you will see what you can do.

Fixing plan regressions in SQL Server 2016

SQL Server 2016 exposes execution statistics about the queries and the plans that were executed. You can query these views and find information about the different plans that are executed for a query and compare their differences. Here is one query that you can execute to find different plans for T-SQL queries:

select p.query_id, p.plan_id, rsi.start_time, rsi.end_time, rs.avg_cpu_time, rs.avg_duration
from sys.query_store_plan p
     join sys.query_store_runtime_stats rs on p.plan_id = rs.plan_id
     join sys.query_store_runtime_stats_interval rsi on rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
order by p.query_id, rs.runtime_stats_interval_id

You can analyze results of this query and identify different plan_id values for the same query and compare their average CPU time and average duration. Once you identify the plan_id that is worse than other plans, you can force SQL Server to use some of the older plan id by taking the query_id and plan_id pairs from the results of this query and execute the following script:

exec sp_query_store_force_plan @query_id = 2, @plan_id = 1804

Once you execute this script, SQL Server will always use the plan with 1804 on a query with id 2.

Fixing plan regressions in SQL Server 2017

Although you have all necessary information in the sys.query_store views, the process of analyzing plans and finding the last good plan that should be forced might still be complex and tedious. Usually you would need to do the following things:

1. You would need to cross-analyze a lot of plans for the same queries and compare their statistics.
2. You would need to find the most recent good plan, because older plans might not be valid anymore.

In SQL Server 2017, this process is easier, because there you can find a new view sys.dm_db_tuning_recommendations that contains one row for each regressed plan and recommended script that you can apply. This is a tuning recommendation that you can apply to fix the problem. In this view, you can find the following information:

1. Reason why SQL Server thinks that you should fix the problem (e.g. average CPU time is changed from 87ms to 695ms),
2. Details about the recommendation that contains id of the query the regressed, the current plan, the recommended plan, and even the script that you can execute on SQL server to fix a regression.

The only thing you need to do is to query this view, get all recommendations with reasons, query detail, and the script that you could execute to fix the issue, e.g.:

SELECT reason,
        JSON_VALUE(details, '$.queryId') as query_id,
        JSON_VALUE(details, '$.regresedPlanId') as regressed_plan,
        JSON_VALUE(details, '$.implementationDetails.script') as script
FROM sys.dm_db_tuning_recommendations

This query will return reason why you should apply this recommendation, id of the query that regressed, id of the regressed plan, and the script that you can apply to fix the problem. SQL Server makes this process much faster and easier – with one simple query you are getting all information you need to identify regressions, difference in the execution, and the script that you should apply.

Automatic plan forcing in SQL Server 2017

If you don’t want to wake-up at night, analyze this information, and manually force last good plan, you can let SQL Server 2017+ automatically fix plan regressions. You just need to enable forcing of the last good plan in the automatic tuning section on the database:

ALTER DATABASE current
     SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON)

Once you execute this query, SQL Server will constantly monitor are there any new recommendations, and automatically force plans if some regression is detected. One important advantage of automatic plan forcing compared to the manual forcing is the fact that SQL Server will also check does the forced plan performs better than the regressed one. If the forced plan is not better, SQL Server will unforce the plan and let Query optimizer produce a new plan.

Conclusion

SQL Server enables you to easily identify plan regressions and fix the problems if they occur. New Automatic tuning feature enables you to delegate monitoring and forcing to the SQL Server. This might make you job much easier and save you from resolving incidents if they occur.