This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.
Till now we saw how to create an Azure SQL Database, create logins, setup the backup strategy now something even more core to DBA activities i,e taking care your indexes and regressed plans.
It might sound magical but that is true Azure SQL Database can create indexes for you, drop redundant indexes and also if it finds any plan regression it automatically chooses the last good plan. For doing this it uses Query Store feature under the covers.
Create Index creates the indexes based upon the usage and is advanced version of missing index feature in SQL Server. It created indexes only when the load is less on the Azure SQL Database.
Drop Index as explained in the above screenshot drops the indexes which are not used since last 90 days.
Force Last Good Plan is similar to the Force query plan in Query Store and so Azure monitors and choose a best plan for the query automatically based upon query history and cached plans.
Below are the default options enabled for automatic tuning.
Automatic tuning can be enabled from the Logical SQL Server or even from the database level.
To enable it using T-SQL use the below command.
ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = OFF)
being a dba 