AUTOMATIC_TUNING

Question

i got this error when run the alter statement in sql server standard edition 2017 and above version on-premises environment, Note that querystore already enabled without any issues
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)

Automatic Tuning is available only in the Enterprise and Developer editions of SQL Server.

Answer 1

You are running Standard Edition. The error message makes it clear that the feature is not available in this edition of SQL Server. This also agree with the information on this page: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver17

Answer 2

That error is expected on SQL Server 2017 Standard edition on-premises.

FORCE_LAST_GOOD_PLAN under AUTOMATIC_TUNING is supported only in Enterprise edition for SQL Server 2017 on-premises. In Standard edition, the option can show as not supported, and sys.database_automatic_tuning_options.reason_desc can return NOT_SUPPORTED, described as available only in SQL Server Enterprise edition.

What can be done instead:

1. Keep Query Store enabled in Read-Write mode.
   • AUTOMATIC_TUNING ... FORCE_LAST_GOOD_PLAN = ON requires Query Store to be enabled and in Read-Write mode, but that alone does not override edition limits.
2. Check the automatic tuning state and reason:

      SELECT *
      FROM sys.database_automatic_tuning_options;
   

   If reason_desc = NOT_SUPPORTED, the edition does not support the feature.
3. Use manual plan correction with Query Store.
   • Identify regressed queries.
   • Force the last known good plan with sp_query_store_force_plan.
   • Monitor performance.
   • Unforce later with sp_query_store_unforce_plan so the optimizer can resume choosing plans.
4. For SQL Server 2017 specifically, sys.dm_db_tuning_recommendations can show potential plan regressions and recommended actions, even when recommendations are not automatically applied.

Key points:

• Query Store being enabled is necessary, but not sufficient.
• On SQL Server 2017 Standard edition, automatic tuning is not available.
• On-premises automatic tuning support is edition-limited compared to Azure SQL.

---

• Automatic tuning
• sys.database_automatic_tuning_options (Transact-SQL)
• ALTER DATABASE SET options (Transact-SQL)
• Editions and supported features of SQL Server 2017