Azure SQL Database – Automatic Index Tuning

Introduction: -

We live in an era of “Automation”. Planes run on autopilot mode, cars will be self-driven, so why not have auto indexed databases? This article will discuss the automatic index tuning feature available in Azure SQL Database.

Problem Statement:

Application development is moving to a rapid pace and its very common for application to design and deliver it to multiple customers. It is even more common in the cloud where solutions are delivered as a "software as a service" and it is way too challenging for an application development team to analyze the workload of an individual database, design specific indexes for them and tune them ...

Automatic optimization in Azure SQL Database

Azure SQL Database, SQL Server's Cloud Platform as a Service (PAAS) offer tried to solve the above problem by introducing the "automatic tuning". To date and the "automatic adjustment" has the following characteristics

Automatic plan correction - Regressions of the plan or sudden changes to the query plan leading to performance degradation are tracked and the last available good plan is forced

Automatic index adjustment:  the automatic index adjustment function of the Azure SQL Database will study the workload executed in relation to the database, identify the useful indexes and create them automatically. Automatic index adjustment will also remove unused indexes for 93 days or duplicate indexes present.

How to enable automatic index adjustment

Once connected to Azure Portal (portal.azure.com), you can activate it by accessing SQL databases-> Click on the database -> Automatic adjustment

· Force plan -> Enable automatic plan correction

· Create / delete index -> allows automatic indexing

Features can be enabled at the server level and can also be inherited at the database level.

TSQL Script for enabling automatic index tuning provided below

ALTER DATABASE DBName         

SET AUTOMATIC_TUNING (CREATE_INDEX = ON, 
      DROP_INDEX = ON)

Example:

To test the operation of automatic indexing, the following operations were performed
· A sample database with 5 tables without index has been created
· 10 to 15 queries selected on the tables were executed several times several timesover3to4hours
· After a day, automatic indexing found the recommendations and deployed them when there was less or no workload on the database. Automatic indexing requires at least one day of monitoring to identify recommendations
This can be checked on the Azure portal by clicking on "Database name" -> "Performance recommendation"

Key pointers for automatic index adjustment

· Indexing recommendations are obtained using Advanced Artificial Intelligence rules applied to the monitored workload. Recommendations should be much more accurate than previous SSMS missing index recommendations

· The best part of the feature is that it is not blocking when deploying new indexes.

· Index changes would not be deployed and will be postponed if DTU usage was greater than 80% at any time in the last 20 minutes

· Automatically created indexes have no dependency on columns. When the column with the automatically created index is deleted, the index is also deleted

· Automatically created indexes can be tracked via sys. index - auto_created column

Learn - Adapt - Check

One of the main features of automatic index adjustment is the ability to monitor the impact of changes made via automatic tuning. Azure SQL Database closely monitors automatically created or deleted indexes and checks to see if it has achieved the desired performance improvement. If this is not the case, the change is automatically canceled to avoid any negative impact on the database. Canceled changes can also be tracked from sys.dm_db_tuning_recommendations DMV.

Suggested improvements

· Automatic indexing is available only as an Azure SQL Database feature and is not available on SQL Server on-premises. It would be wonderful if the functionality could be extended to SQL Server on site.

· Automatic index adjustment also needs to be extended to resolve index fragmentation issues.

Conclusion

Automatic index tuning simplifies one of the main tasks of a database administrator and a developer. Automatic index adjustment may not be 100% perfect for getting the right choices all the time, but it's certainly a step in the right direction. Expect Microsoft to release more of these features in the coming days, not just in indexing, but in all areas of database maintenance.

For More Information about SQL Azure Online Training   ClickHere   

Ph No: +91-9989971070, E-Mail ID: online@visualpath.in