Azure SQL Database Automatic Tuning

In this blog we will learn about automatic tuning of your Azure SQL Database. Being a DBA, We all are familiar with Database or SQL Statements Tuning which is the process of ensuring that the SQL database or statements issued by an application run in the fastest possible time. In other words, tuning SQL statements is finding and taking the fastest route to answer your query, just like discovering the fastest route to your home after work.

In Azure we have features like Query Store feature under the covers from where 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.

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)

That's all in this post. If you liked this blog and interested in knowing more about Azure, Please Like, Follow, Share & Subscribeto www.ImJhaChandan.com.