| title | description | services | ms.service | ms.subservice | ms.custom | ms.devlang | ms.topic | author | ms.author | ms.reviewer | manager | ms.date |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Apply performance recommendations - Azure SQL Database | Microsoft Docs |
Use the Azure portal to find performance recommendations that can optimize performance of your Azure SQL Database. |
sql-database |
sql-database |
performance |
conceptual |
danimir |
v-daljep |
carlrab |
craigg |
04/01/2018 |
You can use the Azure portal to find performance recommendations that can optimize performance of your Azure SQL Database or to correct some issue identified in your workload. Performance recommendation page in Azure portal enables you to find the top recommendations based on their potential impact.
To view and apply performance recommendations, you need the correct role-based access control 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 Azure portal:
- Sign in to the Azure portal.
- Go to All services > SQL databases, and select your database.
- 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:
| Impact | Description |
|---|---|
| High | High impact recommendations should provide the most significant performance impact. |
| Medium | Medium impact recommendations should improve performance, but not substantially. |
| Low | Low impact recommendations should provide better performance than without, but improvements might not be significant. |
Note
Azure SQL Database needs to monitor activities at least for a day in order to identify some recommendations. The Azure SQL Database can more easily optimize for consistent query patterns than it can for random spotty bursts of activity. If recommendations are not currently available, the Performance recommendation page provides a message explaining why.
You can also view the status of the historical operations. Select a recommendation or status to see more information.
Here is an example of "Create index" recommendation in the Azure portal.
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 click 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.
You can review and accept recommendations one at a time.
-
On the Recommendations page, select a recommendation.
-
On the Details page, click Apply button.
Selected recommendation are applied on the database.
If your list of recommendations contains items that you want to remove from the list, you can discard the recommendation:
- Select a recommendation in the list of Recommendations to open the details.
- Click Discard on the Details page.
If desired, you can add discarded items back to the Recommendations list:
- On the Recommendations page, click View discarded.
- Select a discarded item from the list to view its details.
- Optionally, click Undo Discard to add the index back to the main list of Recommendations.
Note
Please note that if SQL Database Automatic tuning is enabled, and if you have manually discarded a recommendation from the list, such recommendation will never be applied automatically. Discarding a recommendation is a handy way for users to have Automatic tuning enabled in cases when requiring that a specific recommendation shouldn’t be applied. You can revert this behavior by adding discarded recommendations back to the Recommendations list by selecting the Undo Discard option.
You can set the Azure SQL 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.
-
On the Recommendations page, click Automate:
-
Select actions to automate:
Note
Please note that DROP_INDEX option is currently not compatible with applications using partition switching and index hints.
Once you have selected your desired configuration, click Apply.
Select any recommendation and then click 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 so it is suggested that you monitor these 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).
Recommendations that are in a Pending, Validating, or Success status can be canceled. Recommendations with a status of Executing cannot be canceled.
- Select a recommendation in the Tuning History area to open the recommendations details page.
- Click Cancel to abort the process of applying the recommendation.
Applying a recommendation might not happen instantaneously. The portal provides details regarding the status of recommendation. The following are possible states that an index can be in:
| Status | Description |
|---|---|
| Pending | Apply recommendation command has been received and is scheduled for execution. |
| Executing | The recommendation is being applied. |
| Validating | Recommendation was successfully applied and the service is measuring the benefits. |
| Success | Recommendation was successfully applied and benefits have been measured. |
| Error | An error occurred during the process of applying the recommendation. This can be a transient issue, or possibly a schema change to the table and the script is no longer valid. |
| Reverting | The recommendation was applied, but has been deemed non-performant and is being automatically reverted. |
| Reverted | The recommendation was reverted. |
Click an in-process recommendation from the list to see more information:
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:
- Select a successfully applied recommendation in the Tuning history area.
- Click Revert on the recommendation details page.
After recommendations are successfully implemented (currently, index operations and parameterize queries recommendations only), you can click Query Insights on the recommendation details page to open Query Performance Insights and see the performance impact of your top queries.
Azure SQL Database provides recommendations for improving SQL database performance. By providing T-SQL scripts, you get assistance in optimizing your database and ultimately improving query performance.
Monitor your recommendations and continue to apply them to refine performance. Database workloads are dynamic and change continuously. Azure SQL Database continues to monitor and provide recommendations that can potentially improve your database's performance.
- See Automatic tuning to learn more about the automatic tuning in Azure SQL Database.
- See Performance recommendations for an overview of Azure SQL Database performance recommendations.
- See Query Performance Insights to learn about viewing the performance impact of your top queries.