Azure Sql Automatic tuning is not working

Question

How to fix Azure Sql Automatic Tuning? on the Azure portal, the recommendations page never loads. and when I run: select * from sys.database_automatic_tuning_options I get the following error:

GetRecommendedActions operation failed. Error occured at Management Service. Http error code: 400. Response: <Error xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ErrorDescription>Invalid JSON primitive: -1.797693134862316e+308.</ErrorDescription>

<ErrorDetails>ManagementServiceException: Invalid JSON primitive: -1.797693134862316e+308. ---> ArgumentException</ErrorDetails>

</Error>.

When I run the diganostics from azure portal I received:

We are unable to run the diagnostic at this time. This might be due to a missing property, or a resource not being selected.

How can I Solve this problem, some Databases within our Sql elastic pool are showing the error message while others shows the empty table.

Answer 1

Hi Juan,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you are encountering an issue with Azure SQL Automatic Tuning.

Below are the recommended steps to troubleshoot and resolve the issues:

• Enable Automatic Tuning: Ensure that automatic tuning is enabled on your server and database. You can do this through the Azure portal, REST API calls, or T-SQL commands. Navigate to your server in the Azure portal, select "Automatic tuning" in the menu, and enable the desired tuning options. https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable?view=azuresql
• Check for Configuration Issues: The error message you received indicates an issue with the JSON primitive. This might be due to a misconfiguration. Verify that the automatic tuning configuration is correctly set up on both the server and database levels. You can manage the automatic tuning configuration at the server level so that the same settings apply to all databases automatically. https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable?view=azuresql
• Review Performance Recommendations: Azure SQL Database provides performance recommendations through database advisors. These recommendations can help you optimize your database performance. Ensure that you have reviewed and applied the recommendations provided by the database advisors. https://learn.microsoft.com/en-us/azure/azure-sql/database/database-advisor-implement-performance-recommendations?view=azuresql
• Monitor and Troubleshoot: Use the built-in performance monitoring and alerting features of Azure SQL Database to monitor the status of your databases. The Query Store feature records the performance of your queries in real-time and helps identify potential performance issues. https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview?view=azuresql

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.

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.