To accelerate the performance of Queries on tables, Index Management is significant. Indexes improve the Queries performance while reading data but can perform slowly while updating a record. Unused Indexes can also impact Query performance, so it is recommended to remove unused Indexes.

To facilitate Index management, Azure SQL Database has a built-in data service that will regularly monitor Queries and perform Index analysis to optimize the performance workload. After analyzing the Indexes, the service generates performance recommendations. The user can review these recommendations and manually apply them or enable auto-tuning options, to apply them automatically.

Performance Recommendations

Azure SQL Database continuously monitors the database analyzing the Queries and usage history of the database and based on that it provides automated recommendations which help to promote the performance. These automated recommendations for performance are in three categories. One can view these recommendations easily in the Azure portal.

1. Create Index Recommendation

Azure SQL database will keep monitoring the Queries that run over the table and identifies Indexes that would help to boost up the performance of the table. Based on this, a new create Index recommendation is created.

2. Drop Index Recommendation

During continuous monitoring, Azure SQL database will not only look for missing Indexes but also it would analyze the performance of existing Indexes.

Based on this analysis, the Drop Index recommendation will be created when:

  • The Index is stale for a term of 93 days
  • The Index is redundant i.e., two or more Indexes with the same column, partition schema and filters

3. Parameterize Queries recommendations

When one or more Queries recompiled constantly but generate the same query execution plan, it leads to creating a Parameterized Queries recommendation. To improve the performance and reduce resource usage, this recommendation will apply a Forced parameterization which allows caching query plans and can be reused in future

 

To view Performance Recommendations

From the Azure portal,

  1. Login to the Azure portal and navigate to your Azure SQL database
  2. On the left panel, there is an option ‘Performance Recommendations’ under Intelligent Performance

Azure SQL database takes at least a day to monitor the activities and to generate recommendations.
auto-index-management-azure-sql-database

The recommendations are sorted based on the performance impact.

Impact

Description

High

Recommendations which will give a great performance improvement
Medium

Recommendations that will provide a better performance

Low

Recommendations that will support a considerably better performance

To view the recommendation information and the historical operations, click onto the recommendation.
auto-index-management-azure-sql-database

Recommendations might not be applied instantaneously as Azure makes sure it does not impact the current workload. The portal also provides details regarding recommendation status.

Status

Description

Pending

Apply recommendation command has been received and is scheduled for execution.

Executing

The recommendation is being applied.

Validating

The recommendation was successfully applied, and the service is validating the performance.

Success

The recommendation was successfully applied, and performance has been validated.

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 still no performance improvement and is being automatically reverted.

Reverted

The recommendation was reverted.

To apply performance recommendations

Azure SQL Database provides you below three options to apply these recommendations:

  1. Apply individual recommendations manually one at a time at the portal.
  2. To automatically apply performance recommendations, enable Automatic tuning option
  3. Apply recommendations through T-SQL Script

Steps to Auto-Tune the Azure SQL database

We can enable the auto-tuning option either through the Azure portal or T-SQL. In Azure Portal, Navigate to Auto Tuning in Azure SQL database service.

CREATE INDEX To identify and apply the Indexes that will boost up the performance and also automatically validate the Queries performance
DROP INDEX To identify and drop the unused Indexes for a long period(90 days) and duplicate Indexes
FORCE LAST GOOD PLAN (automatic plan correction) To identify the SQL Queries that have a most recent plan which performed better than the current plan and Queries that not use regressed plan but a last known good plan

Recommendations might not be applied instantaneously as Azure makes sure it does not impact the current workload. The portal also provides details regarding recommendation status.

StatusDescription
PendingApply recommendation command has been received and is scheduled for execution.
ExecutingThe recommendation is being applied.
ValidatingThe recommendation was successfully applied, and the service is validating the performance.
SuccessThe recommendation was successfully applied, and performance has been validated.
ErrorAn 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.
RevertingThe recommendation was applied but still no performance improvement and is being automatically reverted.
RevertedThe recommendation was reverted.

To apply performance recommendations

Azure SQL Database provides you below three options to apply these recommendations:

  1. Apply individual recommendations manually one at a time at the portal.
  2. To automatically apply performance recommendations, enable Automatic tuning option
  3. Apply recommendations through T-SQL Script

Steps to Auto-Tune the Azure SQL database

We can enable the auto-tuning option either through the Azure portal or T-SQL. In Azure Portal, Navigate to Auto Tuning in Azure SQL database service.

CREATE INDEX To identify and apply the Indexes that will boost up the performance and also automatically validate the Queries performance
DROP INDEX To identify and drop the unused Indexes for a long period(90 days) and duplicate Indexes
FORCE LAST GOOD PLAN (Automatic plan correction) To identify the SQL Queries that have a most recent plan which performed better than the current plan and Queries that not use regressed plan but a last known good plan

On the desired state, you can enable or disable the options or settings can also be inherited from SQL Server directly.

auto-index-management-azure-sql-database

To set auto tuning options via T-SQL

 

Database workloads are dynamic and Azure SQL Database provides recommendations to enhance the query performance. It can also continuously monitor the recommendations and apply them to the required tables automatically.

Reach out to us for implementation details or questions. Learn more about Visual BI’s Microsoft Azure offerings here.

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com