Azure sql database automatic tuning force plan is 'disabled by system' and can't be changed.

Question

Azure sql database automatic tuning force plan is 'disabled by system' and can't be changed.

Carl Matthewman 0

Properties are inherited from the server (which inherits from azure default of 'on') but changing the setting to inherit/on has no effect.
Query store is enabled, read write and 15% free. Other settings are default. How can I turn Force Plan on here?
User's image

1 answer

Your answer

Answer 1

Obinna Ejidike 815

Hi Carl Matthewman

Thanks for using the Q&A platform.

Based on your provided information, your current max is only 100 MB. Can you increase it to at least 512 MB (1 GB recommended)?

That low space might be triggering the system to disable tuning, particularly the force plan. Kindly check the documentation below:

https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use?view=azuresql#recommended-retention-and-capture-policy
https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable?view=azuresql#troubleshooting

If the response was helpful, please feel free to mark it as “Accepted Answer” and consider giving it an upvote. This helps others in the community as well.

Regards,

Obinna.

Saraswathi Devadula 2,665 Reputation points Microsoft External Staff Moderator

2025-04-22T08:46:28.75+00:00

Hi Carl Matthewman

Just checking in to see if the above answer provided by @Obinna Ejidike helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Vijayalaxmi Kattimani 2,330 Reputation points Microsoft External Staff Moderator

2025-04-23T09:19:10.16+00:00

Hi Carl Matthewman

Just checking in to see if the above answer provided by @Obinna Ejidike helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Carl Matthewman 0 Reputation points

2025-04-24T14:20:25.9866667+00:00

I've applied all these options and also set the 'force plan' to not inherit from server. I've set the desired state to ON, but the current state remains as 'OFF Disabled by System'
Vijayalaxmi Kattimani 2,330 Reputation points Microsoft External Staff Moderator

2025-04-24T14:26:14.29+00:00

Hi @Carl Matthewman,

We would like to thank you for your feedback provided on survey. We found your recent engagement on this question was rated low. However, we were able to share some additional information that could helped in the later comment. Would you please re-evaluate your feedback.
Vijayalaxmi Kattimani 2,330 Reputation points Microsoft External Staff Moderator

2025-04-24T15:01:10.3533333+00:00
Hi Carl Matthewman,

Greetings!

We apologize for the inconvenience caused.

Thanks for the details and screenshot.

From what you are describing, you are encountering an issue with Azure SQL Database Automatic Tuning, where the Force Plan feature is "Disabled by system" and can't be turned on even though Query Store is enabled and in read-write mode, with space available.

Yet you mentioned that even changing settings at the server level has no effect. This is often due to:

The Query Store not being "fully initialized" for the purpose of automatic tuning.

Recent resets, lack of plan regressions, or an internal telemetry trigger that disables tuning due to observed issues.

System-determined constraints, such as internal Microsoft logic based on your database’s performance characteristics, engine version, or service tier.

Here are some insights and potential solutions to address your issue:

Check for Diagnostic Messages: Run the following T-SQL to inspect the tuning recommendations and diagnostics:

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

Also check this DMV, If the reason_desc says something like "Query Store is not collecting data" or "Insufficient data", that means Query Store hasn’t yet gathered enough runtime plans for the system to allow Force Plan.

SELECT reason, reason_desc FROM sys.database_automatic_tuning_mode;

Force it via PowerShell or CLI (if supported): Even when the portal and SSMS disable the toggle, sometimes setting it via CLI works:

Azure CLI:

az sql db update \ --name <your-db-name> \ --resource-group <your-resource-group> \ --server <your-server-name> \ --auto-tuning '{"forceLastGoodPlan":"ON"}'

PowerShell:

Set-AzSqlDatabaseAutomaticTuning ` -ResourceGroupName "<your-resource-group>" ` -ServerName "<your-server-name>" ` -DatabaseName "<your-db-name>" ` -ForceLastGoodPlan "On"

Wait Until Query Store Builds Enough Data: In some cases, after enabling Query Store, SQL Server/Azure SQL needs time (and enough query history) to determine plan regressions before allowing force plan. Let it run for a few more days under normal load.

I hope this information helps. If the issue still persists, please let us know here. We will respond with more details and try to help you.
Vijayalaxmi Kattimani 2,330 Reputation points Microsoft External Staff Moderator

2025-04-25T09:44:11.19+00:00

Hi @Carl Matthewman

We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
Vijayalaxmi Kattimani 2,330 Reputation points Microsoft External Staff Moderator

2025-04-28T08:44:00.21+00:00

Hi @Carl Matthewman

We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.

Share via

Azure sql database automatic tuning force plan is 'disabled by system' and can't be changed.

1 answer

Your answer