Why is Azure SQL database "force plan" automatic tuning disabled by the system?

Question

I have set the the "desired state" to ON for the three automatic tuning options for my Azure SQL database (i.e. force plan, create index, drop index).

The database compatibility level is 150 and the Query Store operation mode is "read write".

However, the force plan setting remains at OFF, with the message "disabled by system". This is confirmed by querying sys.database_automatic_tuning_options.

I'm keen to take advantage of the automated plan forcing. What could be causing this, and is there anything I can do to fix it?

Answer 1

I could find this documented in the official MS doc here

Automated recommendation management is disabled

In case of error messages that automated recommendation management has been disabled, or simply disabled by system, the most common causes are:

• Query Store is not enabled, or
• Query Store is in read-only mode for a specified database, or
• Query Store stopped running because it used the allocated storage space.

The following steps can be considered to rectify this issue:

• Clean up the Query Store, or modify the data retention period to "auto" by using T-SQL. See how to configure recommended retention and capture policy for Query Store.
• Use SQL Server Management Studio (SSMS) and follow these steps:
  • Connect to the Azure SQL Database
  • Right click on the database
  • Go to Properties and click on Query Store
  • Change the Operation Mode to Read-Write
  • Change the Store Capture Mode to Auto
  • Change the Size Based Cleanup Mode to Auto

Answer 2

Please read the following excerpt from a Microsoft article about the subject.

Please note that although you might have one of the auto tuning options set to ON, the system might decide to temporarily disable automatic tuning if it deems necessary to protect the workload performance. It also could be that if the Query Store is not enabled on a database, or if it is in a read-only state, this will also render automatic tuning as temporarily disabled. In this case, view of the current state will indicate “Disabled by the system” and the value of the column actual_state will be 0.

The first line of the excerpt mentions that the system may decide to temporarily disable automatic tuning options to protect the workload performance. Read the complete article here.