Is CREATE/DROP INDEX Automatic tuning on the Roadmap for SQL managed instances?

Question

Hello,

Azure SQL DB's automatic tuning feature is incredibly helpful for us. Is there any plan to bring these features to SQL managed instances in the future?

Thank you,

Greg

Answer 1

The artificial intelligence used by Azure SQL usually proliferates the creation of indexes. When you look closely at the indexes offered/created by this feature you will often notice suggested indexes on the same table just differ of one another by just one or two columns. My suggestion to you is to analyze the indexes proposed by Automatic Tuning and consolidate them yourself, then create the indexes that came out of that consolidation you did.

The proliferation on indexes certainly will do good to SELECT queries but will also affect negatively the performance of INSERT and UPDATE statements.

Answer 2

Hi @ adm.Greg Gardner

Thanks for the question and using MS Q&A platform.

Yes, CREATE/DROP INDEX Automatic Tuning is already available for Azure SQL Managed Instance. This feature is called Automatic Index Management, and it provides an automated way to optimize your database's performance by continuously monitoring and tuning. Automatic tuning for SQL Managed Instance only supports FORCE LAST GOOD PLAN.

To enable Automatic Index Management, you can use the Azure portal or PowerShell. Once enabled, it will continuously monitor your database and make index recommendations based on the workload patterns. You can review the recommendations and choose to apply them manually or let Automatic Index Management apply them automatically.

For Additional Information, please refer to this document: Automatic tuning for Azure SQL Managed Instance.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.