Automatic index management in Azure SQL database

Azure SQL Database has a built-in intelligence mechanism that can automatically tune and improve performance of your queries by dynamically adapting the database schema to your workload. In Azure SQL Database, you don’t need to worry about the index analysis and design, because Azure SQL Database learns about your workload and ensures that your data is always optimally indexed.

Why do you need index management?

Indexes speed up some of your queries that read data from the tables; however, they might slowdown the queries that update data. You need to carefully analyze when to create a new index and what columns you need to include in the index. Some indexes might not be needed after some time; therefore, you would need to periodically identify them and drop them if they bring no benefits. If you ignore the unused indexes, performance of the queries that update data would be decreased without any benefit on the queries that read data. Unused indexes also affect overall performance of the system because additional updates requires unnecessary logging and increases WRITELOG wait statistics.

Finding the optimal set of indexes that will improve performance of the queries that read data from your tables that at the same time minimally impact updates, might require continuous and very complex analysis.

Some database systems try to solve index management problem by introducing advanced indexes that automatically include as much as possible fields in the index. In these cases, you don’t need to think about the indexes that should be added on your data, because one general-purpose index can be used in a variety of queries that read data. However, these indexes still affect update performance, so they are good solution for unpredictable read-only workloads.

Azure SQL Database supports a variety of workload types, so it cannot assume that one index might solve all performance problems. Also, it will not enforce users to do all hard and tedious index management analysis.

Azure SQL Database uses built-in intelligence and advanced rules that analyze your queries, identify what kind of indexes would be optimal for your current workloads, and what indexes might be removed. This way, Azure SQL Database ensures that you have a minimal necessary set of indexes that optimize most of your queries that read data, with the minimized impact on the queries that updates.

These are the best practices based on the experience of the thousands database experts who manage their own database last 20 years. The best practices are built-in directly into the Azure SQL Database engine and available to all Azure SQL Database users.

How to identify indexes that need to be changed in your database?

Azure SQL Database makes index management process easy. Instead of the tedious process of manual workload analysis and index monitoring, Azure SQL Database analyzes your workload, identifies the queries that could be executed faster if you create an index, identifies indexes that are not used in a longer period of time, and identifies duplicated indexes in the database.

On the Azure portal, you can find a set of recommendations that you might apply to optimize performance of your database by creating missing indexes or dropping duplicate indexes.

In this report, you might see the actions that you might execute, information about the index that you should create, or name of the index that you might drop. All recommendations have associated impact that indicates the gain that you can get if you apply this recommendation

Automatic index management

If you find that the built-in rules improve the performance of your database – you might let Azure SQL database automatically manage your indexes, decide when to create necessary indexes and remove unused indexes.

You can go to Azure portal, and enable Azure SQL Database to automatically create or drop indexes:

You can let Azure SQL Database to both create necessary indexes and remove unnecessary indexes, or choose which of these options should automatically done and what you want to do manually. You can always change your decision and turn on/off some of these automatic tuning options.

One important feature in Automatic index management is continuous verification of the actions. When Azure SQL Database executes create index or drop index action, a continuous monitoring process analyzes performance of your workload, and analyzes did the action improve the performance. If the assumption was not correct or if it didn’t bring significant improvement – the action will be immediately reverted. This way, Azure SQL Database ensures that automatic actions will not affect performance of your workload.

Summary

Azure SQL Database has intelligent automatic tuning mechanism that takes care of your indexes. If you cannot identify and monitor your indexes, you can let the Azure SQL database do index management for you, tune your database, and ensure that your data structures dynamically adapts to your workload.

Find more information about automatic index management on Azure Documentation.

Comments

• Anonymous
  July 28, 2017
  q: how does the background index management (specifically adding indexes) affect DTUs? I assume that creating indexes, whether performed by user request or by automatic recommendation, is still going to consume DTUs against the tier... seems like such a feature would benefit from a schedule so that the impact of DTUs can be minimized.