Times are changing, 10 years ago I would never have thought that self-tuning databases would be available as a packaged product. I was testing out SQL Server 2017 Automatic Tuning recently and I ended up with the following situation. Below shows an image from the query store.

Before we begin any further let’s do a little recap. Automatic tuning in SQL Server 2017 notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems, this is also available in Azure SQL Database.

There are 2 parts to it, automatic plan correction and automatic index management, for SQL Server 2017, automatic index management it IS NOT part of the product.

To switch automatic plan correction on you will need to run the following code against your database.

ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);

Then you can verify that the setting is on.

SELECT *
FROM sys.database_automatic_tuning_options
WHERE name = 'FORCE_LAST_GOOD_PLAN';

Anyway, back to my example.

For my query ID 1 I have 2 plan IDs, Plan ID 2 has been forced by automatic tuning. The key differences between the 2 plans is that plan 2 uses a stream aggregate (plus an expensive sort) whereas plan 1 uses hash match.

If you run the following query you will be able to see the status of the recommendation.

SELECT reason, score,
	JSON_VALUE(state, '$.currentValue') state,
	JSON_VALUE(state, '$.reason') state_transition_reason,
    JSON_VALUE(details, '$.implementationDetails.script') script,
    planForceDetails.*
FROM sys.dm_db_tuning_recommendations
  CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            [new plan_id] int '$.regressedPlanId',
            [recommended plan_id] int '$.recommendedPlanId'
          ) as planForceDetails;

This is in a verification state meaning that the recommendation is applied by Database Engine and internal verification process is comparing performance of the forced plan with the regressed plan.

However, this forcing of the plan will not always be the right decision. I basically now run a stored procedure where we are sorting and grouping many hundred thousand rows. The currently forced plan would not be the optimal one.

EXEC dbo.Prices 772
GO 50

I know this and even get warnings from the execution plan. Operator used tempdb to spill data during execution with spill level 1 and 1 spilled thread(s), Sort wrote 7381 pages to and read 7381 pages from tempdb with granted memory 167968KB and used memory 167968KB

Will the database engine pick up on this?

During the execution of the stored procedures it switches its plan from the stream aggregate to the hash.

The plan has been unforced by the database engine.

Just to prove this fact re-run the query where we check the status column in sys.dm_db_tuning_recommendations.

SELECT reason, score,
	JSON_VALUE(state, '$.currentValue') state,
	JSON_VALUE(state, '$.reason') state_transition_reason,
    JSON_VALUE(details, '$.implementationDetails.script') script,
    planForceDetails.*
FROM sys.dm_db_tuning_recommendations
  CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            [new plan_id] int '$.regressedPlanId',
            [recommended plan_id] int '$.recommendedPlanId'
          ) as planForceDetails;

It has definitely been reverted. A very intelligent tool, far more intelligent than myself.