Introduction: -
We
live in an era of “Automation”. Planes run on auto pilot mode, cars will
soon be auto 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 at a rapid pace and its very common for application teams
to develop a template database design and deliver it to multiple
customers. It is even more common in cloud where solutions are delivered
as “software as a service” and it is way too challenging for application
development team to analyze the workload of individual database, design
specific indexes for them and tune them.
Automatic tuning in Azure SQL Database
Azure
SQL Database, the cloud Platform as a service (PAAS) offering of SQL Server has
attempted to address the above problem by introducing “Automatic tuning”. As of
date, “automatic tuning” has the following features
· Automatic plan correction
– Plan regressions or sudden change in query plan resulting in performance
degradation are tracked and last good plan available is forced. Automatic plan
correction is already well explained over here
· Automatic Index Tuning:
Automatic index tuning feature of Azure SQL Database will study the workload
executed against the database, identify useful indexes and create them
automatically. Automatic index tuning will also drop indexes that are unused
over 93 days or any duplicate indexes present.
How to enable automatic index tuning
Once
logged in to Azure Portal (portal.azure.com) one can enable the same by
navigating to SQL Databases-> Click on the database -> Automatic tuning
·
Force plan -> Enables Automatic Plan Correction
·
Create / Drop Index -> enables automatic index tuning
Features
can be enabled at server level and can be inherited at the database level as
well.
TSQL
Script for enabling automatic index tuning provided below
ALTER
DATABASE DBName
SET
AUTOMATIC_TUNING (CREATE_INDEX = ON, DROP_INDEX = ON)
Example:
To
test how Auto Indexing works, following things were performed
·
A sample database with 5 tables with no indexes was created
·
10 to 15 select queries against the tables were executed repeatedly few
thousand times over 3 to 4 hours
·
After one day, automatic indexing found the recommendations and rolled them out
when there was less or no workload on the database. Auto Indexing
requires minimum one day of monitoring to identify recommendations
This
can be verified on Azure Portal by clicking on the “Database name” ->
“Performance Recommendation”
The
same can also be verified via the DMV sys.dm_db_tuning_recommendations,
documented clearly over here
Key pointers of Automatic Index Tuning
·
Indexing recommendations are obtained using advanced artificial intelligence
rules applied on the monitored workload. The recommendations are expected to be
lot more accurate than earlier missing index recommendations from SSMS
·
The best part of the feature is it is non-blocking during the rollout of new
indexes.
·
Index changes wouldn’t be rolled out and will be postponed, if DTU usage was
above 80% anytime in the last 20 minutes
·
Automatically created indexes don’t enforce any dependency on the columns. When
the column with automatically created index is dropped, index is dropped as
well
·
Auto created indexes can be tracked via sys.indexes – auto_created column
Learn – Adapt - Verify
One
of the key features of Automatic Index tuning is the ability to monitor the
impact of the changes done via automatic tuning. Azure SQL Database closely
monitors the automatically created or dropped indexes and verifies if it has
achieved the desired performance improvement. If it doesn’t then the change is
automatically reverted to prevent any negative impact to the database. The
reverted changes can be tracked from sys.dm_db_tuning_recommendations DMV
as well.
Suggested enhancements
·
Automatic indexing is available only as an Azure SQL Database feature and is
not available on SQL Server on premise. If would be wonderful if the feature
could be extended to on premise SQL Server.
·
Automatic Index tuning should be extended to fixing fragmentation issues of the
indexes as well.
Conclusion
Automatic
Index Tuning does simplify one of the major tasks of a database administrator
and a developer. Automatic Index Tuning may not be 100% perfect to get the
choices correct all the time but it is certainly a step in the right direction.
Expect Microsoft to release more of such features in the days to come not just
in indexing but also all areas of database maintenance
