I used to be on the fence regarding whether or not Automatic Tuning should be on as the default when creating Azure SQL Databases. A part of me never liked the idea of Azure creating/dropping indexes or forcing plans without my prior approval but then again if it happens to do the right thing at the right time then it’s a pleasurable experience.
I left this option on for few months and thought the whole experience was great. Getting some further clarity from a Program Manager (thanks Veljko) at Microsoft really did make me double think about the setting, in a good way!
Automatic tuning options at the server level are applied to all databases on the server. By default, all databases inherit the configuration from their parent server, but this can be overridden and specified for each database individually. I prefer working at the database level because not all databases are equal.
The setting can be found within the support and troubleshooting section of your SQL Database.
Clicking it will present you with three options. I can see Microsoft adding to this in the future.
What are the options?
- CREATE INDEX that identifies the indexes that may improve performance of your workload, creates the indexes, and verifies that they improve performance of the queries.
- DROP INDEX that identifies redundant and duplicate indexes, and indexes that were not used in the long period of time.
- PLAN REGRESSION CORRECTION that identifies SQL queries that are using execution plan that are slower than previous good plan, and uses the last known good plan instead of the regressed plan.
Me being me I traded some emails with Veljko and received some more really important details that I would like to share with you.
Q. How long until Automatic Tuning picks up recommendations for queries? I was running multiple queries with obvious missing non clustered indexes but yet to see anything?
A. Once we notice that in couple of time intervals the value the index would bring is continuously increasing we will publish the recommendation.
Q. Dropping indexes, I am always nervous. There could be just one important one used rarely. What safe-guards do you have?
A. Duplicates are dropped – where index is identical to another index that exists on the same table and dropping unused indexes we set the time threshold to about 3 months.
Q. How does plan forcing work?
A. Once a plan is forced, there is a back off mechanism, that will stop forcing a plan to see if it can find a better one. This back off period incrementally increases with every iteration. If we force the plan again the back off period is much longer and so on.
There is another question which I can actually answer for you.
Q. What happens If you create an index and it actually makes your query slower?
A. Well the system will revert that change! Look out for the all-important DTU regression figure.
Makes sense to revert that index. I think it is pretty powerful with the way it tracks post implementation of the index, more specifically the queries with improved/regressed performance.
This feature is always watching your workload and trying to figure out what the next best course of action is. Who knows maybe one day it will be able to do index consolidation. This is a setting you should seriously consider especially if you have not got in-house SQL experience.
Hopefully you have found this post helpful.
Filed under: Azure, Azure SQL DB Tagged: Automatic Tuning, Azure, Azure Portal, Azure SQL DB 
