Automatic tuning is not creating indexes on our SQL database

Question

Automatic tuning is not creating indexes on our SQL database

Kevin Hannan 6

We have automatic tuning turned on with Create Index ON, but it no longer creates indexes like it used to.

Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-04T22:46:18.1+00:00

Hi Kevin Hannan,

Greetings!

Could you please share the full error message you saw, or a screenshot if that’s easier? This will help us understand what’s going on and assist you better.
Kevin Hannan 6 Reputation points

2025-06-05T13:12:46.46+00:00

I am not getting any error messages, there is just nothing under Performance History > Tuning Recommendations. It just says "There are currently no operations to display. Once you apply a recommendation, its status will be displayed here."

We previously would have several indexes being tested and verified all the time and the automatic tuning has made many indexes in our database. It is no longer recommending indexes and only does "Force Last Good Plan".
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-05T20:45:37.3266667+00:00
Hi Kevin Hannan,

Thanks for the response.

Here are a few steps you can take to troubleshoot this issue:

Double-check that automatic tuning is still enabled and configured correctly. Sometimes settings can be inadvertently changed.

Ensure your database system is up-to-date. Updates can sometimes affect how features like automatic tuning operate.

Look at the system logs for any warnings or informational messages that might give clues about why index recommendations have stopped.

Consider if there have been changes in your database activity or workload. If the workload has become more stable, there might be fewer opportunities for index recommendations.

If all else fails, consider disabling and then re-enabling the automatic tuning feature. This can sometimes reset the internal state and allow it to start making recommendations again.

Review the latest documentation for your database system to see if there have been any changes to how automatic tuning works.

https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview?view=azuresql&source=recommendations#enable-automatic-tuning

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-advisor-find-recommendations-portal?view=azuresql

If the above options don't work, please share the request details in a private message.

I hope this information helps. Please do let us know if you have any further queries.
Kevin Hannan 6 Reputation points

2025-06-06T00:23:52.4333333+00:00

~~None of the above helped. What details would you like? Also, how do you send a private message?~~ Never mind, I see the private message now

1 answer

Your answer

Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-04T22:46:18.1+00:00

Hi Kevin Hannan,

Greetings!

Could you please share the full error message you saw, or a screenshot if that’s easier? This will help us understand what’s going on and assist you better.
Kevin Hannan 6 Reputation points

2025-06-05T13:12:46.46+00:00

I am not getting any error messages, there is just nothing under Performance History > Tuning Recommendations. It just says "There are currently no operations to display. Once you apply a recommendation, its status will be displayed here."

We previously would have several indexes being tested and verified all the time and the automatic tuning has made many indexes in our database. It is no longer recommending indexes and only does "Force Last Good Plan".
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-05T20:45:37.3266667+00:00

Hi Kevin Hannan,

Thanks for the response.

Here are a few steps you can take to troubleshoot this issue:

Double-check that automatic tuning is still enabled and configured correctly. Sometimes settings can be inadvertently changed.

Ensure your database system is up-to-date. Updates can sometimes affect how features like automatic tuning operate.

Look at the system logs for any warnings or informational messages that might give clues about why index recommendations have stopped.

Consider if there have been changes in your database activity or workload. If the workload has become more stable, there might be fewer opportunities for index recommendations.

If all else fails, consider disabling and then re-enabling the automatic tuning feature. This can sometimes reset the internal state and allow it to start making recommendations again.

Review the latest documentation for your database system to see if there have been any changes to how automatic tuning works.

https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview?view=azuresql&source=recommendations#enable-automatic-tuning

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-advisor-find-recommendations-portal?view=azuresql

If the above options don't work, please share the request details in a private message.

I hope this information helps. Please do let us know if you have any further queries.
Kevin Hannan 6 Reputation points

2025-06-06T00:23:52.4333333+00:00

~~None of the above helped. What details would you like? Also, how do you send a private message?~~ Never mind, I see the private message now

Answer 1

PratikLad 1,825 Microsoft External Staff Moderator

Hi Kevin Hannan,

I have check it with our internal team the issue you are facing of not able to create indexes with automatic tuning.

The indexes are not being created because all missing index recommendations retrieved from the Dynamic Management Views (DMVs) are currently considered insignificant. Specifically:

The ClassificationScore for each recommended index is below the threshold of 33.
As a result, these indexes are flagged with IsRecommendationForPublish = False.
The classification model used by the system is highly tuned to filter out low-impact recommendations to:
- Avoid unnecessary storage consumption.
- Maintain optimal query performance without over-indexing.

Until a recommendation’s ClassificationScore exceeds 33, it will not be published or created automatically.

To resolve this, you can Manually evaluate the index recommendation and Create the index using T-SQL .

Kevin Hannan 6 Reputation points

2025-06-17T17:22:20.7233333+00:00

Can you please provide the T-SQL needed to manually evaluate the index recommendation? I'm not sure which table that data is stored in.
PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-06-18T14:17:10.4166667+00:00

You can check this document https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver17 to create index
PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-06-20T13:38:46.8866667+00:00

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

Please do let us know if you have any further queries.
Kevin Hannan 6 Reputation points

2025-06-24T18:12:56.7866667+00:00

I did not receive an email about this.
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2025-06-27T21:38:55.3366667+00:00

Kevin Hannan

Thank you for reaching out for sharing your feedback.

I have noticed that your recent engagement on this thread was rated low. I am reaching out to see if I can provide further support on this issue. The reason why recommended indexes are not created is because all missing index recommendations taken from the DMV are considered as insignificant (with zero impact).

Please feel free to comment below if you need more clarification. I will be more than happy to help you.

Regards,

Oury

Share via

Automatic tuning is not creating indexes on our SQL database

1 answer

Your answer