Query Store Fundamentals: How Automatic plan correction works?

How wonderful will that be if SQL Server has a way of automatically tune our Queries based on our workloads, amazing! Right?

Thanks to Microsoft for introducing the automatic tuning feature in SQL Server 2017 and available in Azure SQL Database. Automatic tuning has two features. Automatic plan correction and Automatic index correction (Source: Microsoft)

So, what is this automatic option, and how it works?

Once you enable the automatic plan correction, this feature continuously monitors and sniffs the parameter-sensitive queries that are causing the performance problem and monitors the changes in the execution plans, and always looks for the last good plan or the better plan that is newly created. For example, the Automatic plan correction feature has forced a query plan based on the ongoing workload and for any reason, a new plan is created (many reasons can cause the new plan creation like forced plan failures, index rebuilds, statistics updates etc.) and if the existing forced plan is not the best plan any more than the newly generated plan, this feature will go ahead and unforce the existing forced plan and force the newly generated plan. If on the other side, the newly generated plan is not optimal when compared with the existing forced plan, it keeps the existing forced plan as forced and continues to monitor the regression.

Why do you need Automatic plan correction?

As a production DBA, monitoring the generated plans and manually comparing the plans to force the best plan can be difficult. SQL Server engine chooses the optimal plan for our workloads but if your query is generating many execution plans for those parameter sensitive queries, enabling this feature will save a lot of time and energy for you to focus on other tasks. You can also find the reason why the automatic plan correction feature has unforced/forced a plan by using sys.dm_db_tuning_recommendations. It also provides recommendations and corrective action to fix the problem and other information like the reason for the plan change, the detection time for the regression, the script to force the plan that will be optimal and fix the problem. According to Microsoft, “There should be enough regression between the plans for implementing the recommendations, and that is caused when the estimated CPU gain is more than 10 seconds or the errors that are caused by the forced plan is more than the currently generated new plan (Source: Microsoft).

To enable the automatic plan correction:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); 

Let us use the Azure SQL Database for our demo to demonstrate how the automatic plan correction works. Once we enable the automatic plan correction to be turned on, let us make sure the settings are updated and ready to go.

/* Checking the tuning options if the option is enabled */
SELECT *
FROM sys.database_automatic_tuning_options

Create a stored procedure, clearing out the query store clear out the plan cache before beginning the workload

/* Creating the Stored procedure */
IF EXISTS (
		SELECT *
		FROM sys.objects
		WHERE type = 'P'
			AND name = 'Salesinformation'
		)
	DROP PROCEDURE dbo.Salesinformation
GO

CREATE PROCEDURE dbo.Salesinformation @productID [int]
AS
BEGIN
	SELECT [SalesOrderID]
		,[ProductID]
		,[OrderQty]
	FROM [Sales].[SalesOrderDetailEnlarged]
	WHERE [ProductID] = @productID;
END;

/* Clearing the Query store */
ALTER DATABASE [AdventureWorks]

SET QUERY_STORE CLEAR;

/* Clean the procedure cache */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Create the first workload, check for the execution plan in the Query Store. The below parameter will only generate 385 records.

/* Creating the first workload */
EXEC dbo.Salesinformation 942 
GO 50

Clearing out the procedure cache

/* Clean the procedure cache */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Now, create the regression. This parameter generates 237K records. That causes the regression as it is parameter-sensitive.

/* Creating Regression */
EXEC dbo.Salesinformation 707 
GO 10

Run the same stored procedure with the first-time parameter again (Parameter value 942) and check the Query store again.

/* Run the workload again */
EXEC dbo.Salesinformation 942 
GO 30

When you observe, plan ID 1 is automatically forced by the automatic plan correction plan feature. Amazing, right? If you look at the reason by querying the sys.dm_db_tuning_recommendations, you can check the reason for the plan change and other information.

/* Check for the Automatic plan correction in the Query Store */
/* Check for the tuning recommendations */
SELECT *
FROM sys.dm_db_tuning_recommendations

With the continuous monitoring for the regressed plans and performance of your queries, forcing the last known good plan is one of the best features Microsoft offers.

There are certain limitations to the plan forcing. Please check them here.

Summary:

Manually monitoring the forced plans is a tedious task. As we know that there can be better plans generated for the queries with the forced plans later down the road. In those scenarios, it will be really tough to monitor each of those forced plans, unforce, and force the newly generated optimal plan for our queries. The automatic plan correction feature takes that burden by automatically doing the unforcing/forcing of the plans based upon the regressions caused. The last known good plan is ensured to be forced automatically.

Thanks for reading!

Resources:

1. https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver15
2. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql?view=sql-server-ver15#plan-forcing-limitations