Find and apply performance recommendations

Applies to: Azure SQL Database

You can use the Azure portal to find performance recommendations that can optimize performance of your database in Azure SQL Database or to correct some issue identified in your workload. The Performance recommendation page in the Azure portal enables you to find the top recommendations based on their potential impact.

View recommendations

To view and apply performance recommendations, you need the correct Azure role-based access control (Azure RBAC) permissions in Azure. Reader, SQL DB Contributor permissions are required to view recommendations, and Owner, SQL DB Contributor permissions are required to execute any actions; create or drop indexes and cancel index creation.

Use the following steps to find performance recommendations on the Azure portal:

1. Sign in to the Azure portal.
2. Go to All services > SQL databases, and select your database.
3. Navigate to Performance recommendation to view available recommendations for the selected database.

Performance recommendations are shown in the table similar to the one shown on the following figure:

Recommendations are sorted by their potential impact on performance into the following categories:

You can also view the status of the historical operations. Select a recommendation or status to see more information.

Here is an example of the "Create index" recommendation in the Azure portal.

Apply recommendations

Azure SQL Database gives you full control over how recommendations are enabled using any of the following three options:

• Apply individual recommendations one at a time.
• Enable the Automatic tuning to automatically apply recommendations.
• To implement a recommendation manually, run the recommended T-SQL script against your database.

Select any recommendation to view its details and then select View script to review the exact details of how the recommendation is created.

The database remains online while the recommendation is applied -- using performance recommendation or automatic tuning never takes a database offline.

Apply an individual recommendation

You can review and accept recommendations one at a time.

1. On the Recommendations page, select a recommendation.
2. On the Details page, select the Apply button. Selected recommendations are applied on the database.

Remove recommendations from the list

If your list of recommendations contains items that you want to remove from the list, you can discard the recommendation:

1. Select a recommendation in the list of Recommendations to open the details.
2. Select Discard on the Details page.

If desired, you can add discarded items back to the Recommendations list:

1. On the Recommendations page, select View discarded.
2. Select a discarded item from the list to view its details.
3. Optionally, select Undo Discard to add the index back to the main list of Recommendations.

Enable automatic tuning

You can set your database to implement recommendations automatically. As recommendations become available, they are automatically applied. As with all recommendations managed by the service, if the performance impact is negative, the recommendation is reverted.

1. Go to your Azure SQL Database logical server in the Azure portal.

2. Under Intelligent performance, on the Recommendations page, select Automate:

3. Select actions to automate. Once you have selected your desired configuration, select Apply.

   

Manually apply recommendations through T-SQL

Select any recommendation and then select View script. Run this script against your database to manually apply the recommendation.

Indexes that are manually executed are not monitored and validated for performance impact by the service. Monitor manually-created indexes after creation to verify they provide performance gains and adjust or delete them if necessary. For details about creating indexes, see CREATE INDEX (Transact-SQL). In addition, manually applied recommendations will remain active and shown in the list of recommendations for 24-48 hrs. before the system automatically withdraws them. If you would like to remove a recommendation sooner, you can manually discard it.

Cancel recommendations

Recommendations that are in a Pending, Validating, or Success status can be canceled. Recommendations with a status of Executing cannot be canceled.

1. Select a recommendation in the Tuning History area to open the recommendations details page.
2. Select Cancel to abort the process of applying the recommendation.

Monitor operations

Applying a recommendation might not happen instantaneously. The Azure portal provides details regarding the status of recommendation. The following are possible states that an index can be in:

Select an in-process recommendation from the list to see more information:

Revert a recommendation

If you used the performance recommendations to apply the recommendation (meaning you did not manually run the T-SQL script), it automatically reverts the change if it finds the performance impact to be negative. If for any reason you simply want to revert a recommendation, you can do the following:

1. Select a successfully applied recommendation in the Tuning history area.
2. Select Revert on the Recommendation details page.

Monitor performance impact of index recommendations

After recommendations are successfully implemented (currently, index operations and parameterize queries recommendations only), you can select Query Insights on the recommendation details page to open Query Performance Insight for Azure SQL Database and see the performance impact of your top queries.

Related content

• Automatic database tuning
• Database Advisor performance recommendations for Azure SQL Database
• Query Performance Insight for Azure SQL Database
• Query Store