Sql Database Performance Recommendations are not available

Question

Sql Database Performance Recommendations are not available

Stanley de Boer 0

I have a database that I applied some indexes from the Performance recommendations on yesterday, but now it's showing "Recommendations are not available" and the history is not available.

How do I get these to show up again?

Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-03T16:46:01.6833333+00:00

Hi Stanley de Boer,
I’m sorry to hear that you're experiencing issues with the SQL Database performance recommendations not showing up in the Azure Portal. I completely understand how frustrating that can be. There are a few possible reasons for this, such as the Query Store configuration, permissions, or even a temporary issue with the portal. I’d be happy to help you troubleshoot this. Could you let me know if you’ve checked the Query Store settings or if you’re seeing any specific error messages? That information would help us get started on resolving the issue.

Looking forward to your response!
Stanley de Boer 0 Reputation points

2025-04-04T12:51:38.24+00:00

I'm not seeing an error message. I don't think it's a permission issue because I applied some indexes a few days ago, and haven't changed permissions since. How do I check the Query Store Configuration?
Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-04T16:55:41.0833333+00:00
Hi Stanley de Boer,
To check your Query Store configuration in Azure SQL Database, you can use the following steps:
1.Using T-SQL Commands
Run this query to retrieve the current settings of the Query Store

SELECT * FROM sys.database_query_store_options;

This will show you details like whether it's enabled (operation_mode), data collection settings, and retention period.

2Enable Query Store (If Disabled)
If the Query Store is disabled, you can enable it using this command:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

Azure Portal

Alternatively, you can navigate through the Azure Portal:
Go to your SQL Database resource.
Open the "Intelligent Performance" section.
Check the status of the Query Store under the relevant tabs.
If your Query Store settings look fine but you're still not seeing recommendations, it could be due to insufficient workload data for new recommendations to be generated. Let me know if you need further assistance with these steps!
Stanley de Boer 0 Reputation points

2025-04-04T17:07:16.9733333+00:00

I don't see an operation_mode column in database_query_store_options

I did find the sys.dm_db_tuning_recommendations in some other documentation so I can at least see the recommendations, but they're still not showing in the Portal. So I'm going to assume that the Azure Portal is just bugged and nobody knows how to fix it.
Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-04T21:58:02.5333333+00:00
Hi Stanley de Boer
I’m really sorry you’re still having trouble with this. Thanks for checking into the Query Store and sys.dm_db_tuning_recommendations, that’s a great find! It’s awesome that you’ve got a workaround to see the recommendations, but let’s see if we can get them showing in the Portal too. I’ll do my best to help you sort this out.

Why This Might Be Happening:
You’re right that the operation_mode column isn’t in sys.database_query_store_options for Azure SQL Database, it’s actually actual_state, my apologies for the mix-up! That column will tell us if Query Store is ON, OFF, or READ_ONLY, which could affect recommendations showing up. Since you applied indexes a couple of days ago (April 2, 2025, based on your “yesterday” comment—it’s April 4 now), the Portal might need more time or data to refresh, or there could be a caching issue on the UI side.

Steps to Troubleshoot

Here’s what we can try to get those recommendations back in the Portal:

Check Query Store Status Properly

Let’s confirm Query Store is fully active:

Run this T-SQL query in your database:
SELECT actual_state, actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb, data_flush_interval_seconds, interval_length_minutes FROM sys.database_query_store_options;

Look at actual_state_desc. It should say READ_WRITE for recommendations to generate. If it’s OFF or READ_ONLY, turn it on with:
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

If it’s already READ_WRITE, check current_storage_size_mb vs. max_storage_size_mb. If it’s full, it might not be capturing new data—try clearing it:
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

Generate Some Workload

Since you applied indexes recently, the Portal might not show new recommendations until there’s enough fresh query activity for Azure to analyze. Could you run some queries (maybe repeat ones that triggered the original recommendations) for a few hours? Azure needs at least 9-24 hours of solid workload data post-change to update the Portal.

Portal Refresh or Cache Issue

Sometimes the Portal UI lags or caches old data. Try these:

Log out of the Azure Portal, clear your browser cache, and log back in.

Check the Performance Overview under Intelligent Performance again—click around the Recommendations and Tuning History tiles to force a refresh.

If you’ve got multiple browsers, test it in a different one (e.g., Chrome vs. Edge).

Confirm via DMVs (Since You Found sys.dm_db_tuning_recommendations)

You’re spot on with sys.dm_db_tuning_recommendations—it’s a goldmine! Since you see recommendations there but not in the Portal, it suggests the backend is working, but the UI isn’t syncing. Run this to double-check:

SELECT * FROM sys.dm_db_tuning_recommendations;

If it’s populated, the issue is likely Portal-side, not your database.

Since you saw and applied recommendations on April 2 and now it’s “not available” on April 4, it’s possible the Portal’s just slow to update after your index changes, or it’s waiting for more workload data. The fact that sys.dm_db_tuning_recommendations works is a good sign—your database is fine, and it’s likely a UI hiccup.

Next Steps:
Could you try the Query Store check and let me know what actual_state_desc says? Maybe run a few queries too, then check the Portal again in a few hours?

Looking forward to hearing how it goes!
Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-07T16:35:24.6466667+00:00

Hi Stanley de Boer, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-08T01:07:33.7733333+00:00

Hi Stanley de Boer, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Stanley de Boer 0 Reputation points

2025-04-08T19:43:51.0966667+00:00

Hello,

actual_state_desc is READ_WRITE

and
SELECT * FROM sys.dm_db_tuning_recommendations;
does show data

Your answer

Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-03T16:46:01.6833333+00:00

Hi Stanley de Boer,
I’m sorry to hear that you're experiencing issues with the SQL Database performance recommendations not showing up in the Azure Portal. I completely understand how frustrating that can be. There are a few possible reasons for this, such as the Query Store configuration, permissions, or even a temporary issue with the portal. I’d be happy to help you troubleshoot this. Could you let me know if you’ve checked the Query Store settings or if you’re seeing any specific error messages? That information would help us get started on resolving the issue.

Looking forward to your response!
Stanley de Boer 0 Reputation points

2025-04-04T12:51:38.24+00:00

I'm not seeing an error message. I don't think it's a permission issue because I applied some indexes a few days ago, and haven't changed permissions since. How do I check the Query Store Configuration?
Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-04T16:55:41.0833333+00:00

Hi Stanley de Boer,
To check your Query Store configuration in Azure SQL Database, you can use the following steps:
1.Using T-SQL Commands
Run this query to retrieve the current settings of the Query Store

SELECT * FROM sys.database_query_store_options;

This will show you details like whether it's enabled (operation_mode), data collection settings, and retention period.

2Enable Query Store (If Disabled)
If the Query Store is disabled, you can enable it using this command:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

Azure Portal

Alternatively, you can navigate through the Azure Portal:
Go to your SQL Database resource.
Open the "Intelligent Performance" section.
Check the status of the Query Store under the relevant tabs.
If your Query Store settings look fine but you're still not seeing recommendations, it could be due to insufficient workload data for new recommendations to be generated. Let me know if you need further assistance with these steps!
Stanley de Boer 0 Reputation points

2025-04-04T17:07:16.9733333+00:00

I don't see an operation_mode column in database_query_store_options

I did find the sys.dm_db_tuning_recommendations in some other documentation so I can at least see the recommendations, but they're still not showing in the Portal. So I'm going to assume that the Azure Portal is just bugged and nobody knows how to fix it.
Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-07T16:35:24.6466667+00:00

Hi Stanley de Boer, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-04-08T01:07:33.7733333+00:00

Hi Stanley de Boer, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Stanley de Boer 0 Reputation points

2025-04-08T19:43:51.0966667+00:00

Hello,

actual_state_desc is READ_WRITE

and
SELECT * FROM sys.dm_db_tuning_recommendations;
does show data

Share via

Sql Database Performance Recommendations are not available

Your answer