Do you enable this setting to allow automatic tuning to care of all your performance needs? Well not ALL your needs, more so:

• CREATE INDEX
• DROP INDEX
• FORCE LAST GOOD PLAN

You can let this feature tell you about the above and not implement anything, or you could go full robot mode and let it do all of them (and regression work if needed). Microsoft are confident about this feature, they say “Automatic tuning mechanisms are mature and have been perfected on several million databases running on Azure”.

Anyways, I haven’t enable this for this specific database, but it doesn’t stop me thinking about what could’ve been? If you are curious as to what it could’ve done for you then you can execute the following code against your Azure SQL DB. (taken from https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql?view=sql-server-ver15)

  WITH cte_db_tuning_recommendations
AS (SELECT reason,
		score,
		query_id,
		regressedPlanId,
		recommendedPlanId,
		current_state = JSON_VALUE(state, '$.currentValue'),
		current_state_reason = JSON_VALUE(state, '$.reason'),
		script = JSON_VALUE(details, '$.implementationDetails.script'),
		estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
				* (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
		error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
	FROM sys.dm_db_tuning_recommendations
	CROSS APPLY OPENJSON(Details, '$.planForceDetails')
	WITH ([query_id] int '$.queryId',
		regressedPlanId int '$.regressedPlanId',
		recommendedPlanId int '$.recommendedPlanId',
		regressedPlanErrorCount int,
		recommendedPlanErrorCount int,
		regressedPlanExecutionCount int,
		regressedPlanCpuTimeAverage float,
		recommendedPlanExecutionCount int,
		recommendedPlanCpuTimeAverage float
		)
	)
SELECT qsq.query_id,
	qsqt.query_sql_text,
	dtr.*,
	CAST(rp.query_plan AS XML) AS RegressedPlan,
	CAST(sp.query_plan AS XML) AS SuggestedPlan
FROM cte_db_tuning_recommendations AS dtr
INNER JOIN sys.query_store_plan AS rp ON rp.query_id = dtr.query_id
	AND rp.plan_id = dtr.regressedPlanId
INNER JOIN sys.query_store_plan AS sp ON sp.query_id = dtr.query_id
	AND sp.plan_id = dtr.recommendedPlanId
INNER JOIN sys.query_store_query AS qsq ON qsq.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id

I have screen shots of some interesting columns. First look at the reasoning behind the suggestion of forcing a query plan of mine, it says Average query CPU time changed from 1.02ms to 11.13ms. I seem to be missing out here because you can see the current state reason as AutomaticTuningOptionNotEnabled. The great thing about this query shows the execution plans of the regressed vs suggested plan.

Having a quick look between the two this is the key difference – the lazy spool.

If you want to manually apply this setting, then you will need to run the following code and look for the relevant query id and under the script column you will see the command required to force the plan id.

 SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  *(regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

exec sp_query_store_force_plan @query_id = 10862, @plan_id = 5282

Note: The above plan ID is the suggested plan and not the regressed one.