Azure SQL Database- Automatic Index Management

As I begin my education in SQL Server 2017, as well as Azure SQL Database, my attention was caught by the optimizer improvements and especially automatic tuning.

Often referred to with features starting with the naming convention of “dynamic” in Oracle, many automatic features either thrill or frustrate DBAs, no matter what the platform.  The frustration enters with the features introduction and often decreases as the feature matures and becomes more stable.  With the introduction of the Automatic Tuning from Microsoft, there’s a sense of concern, but also some very promising ways Microsoft is introducing these features.

With Automatic Tuning, the Database Engine in Azure SQL Database has Automatic Index Management.  Any DBA is going to take the recommendation for an addition of an index with a grain of salt.  The index has to be justified, not that it will only be used by one query, but that it provides repeated value and it can be justified by the pressure added for every insert, update and delete to additional index.

Where we’re hesitant to add an index, the opportunity to drop an unused index is something we’ll take advantage of whenever its offered.  With Azure SQL Database, the Database Engine can drop unused indexes as it tracks index usage.

In Oracle, we’ve been able to track usage of an index, but only within the monitoring period and if you had end of period reports or end of year processing that wasn’t captured, you could drop indexes that were crucial to the system.  Now, with Oracle 12.2, they’ve started to track, not only if indexes are used or unused ongoing, but how many times the index is used:

select name, total_access_count, total_exec_count, last_used from DBA_INDEX_USAGE where name='IDX_1';

NAME      TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT  LAST_USED
--------- ------------------ ---------------- ---------------------
IDX_1                   85                85     11-03-2017 18:58:43

With Azure SQL Database, Automatic Tuning is continuously monitoring and should be trusted for dropping indexes, but upon further research, it became apparent, that like Oracle, there is a window that the monitoring is for and if you have period or yearly processing that relies on the indexes, you could be impacted.  Per the documentation, “indexes that were not used in a longer period of time that could be removed.” Am I going to choose to drop that index its identified that isn’t required?

Even a bigger question-  would I ever turn on the automation for the create or drop index in the automatic tuning feature?  Nope and NOPE.

I have to admit, when the documentation states that any change that doesn’t improve performance is “immediately reverted” in Automatic Tuning, I’m curious if that the feature is part of index management, as it might be an interesting day in a DBAs life to have indexes disappear and reappear.  I also wonder what that means when we’re talking about a 1TB+ index… 🙂