Source? I’ve been working with SQL Server for a couple of decades and I don’t believe it will automatically create or drop indexes under any circumstances. You might be thinking of Azure SQL DB.
"Automatic tuning, introduced in SQL Server 2017 (14.x), notifies you whenever a potential performance issue is detected and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems." [1]
I have used this in Azure SQL too, but according to that it should be in SQL Server.
Read that link carefully: only automatic plan regression is available in SQL Server, not the automatic index tuning portion. The index tuning portion only applies to Azure SQL DB.
> Automatic index management identifies indexes that should be added in your database, and indexes that should be removed. Applies to: Azure SQL Database
What's the point of asking for a source when you would find it on Google in one minute? Odd way of learning. Not like I brought up some debated viewpoint.
Probably because most of the stuff you'd find in the top search results would include the GP's name.
Just a few sentences later "Automatic tuning in Azure SQL Database also creates necessary indexes and drops unused indexes" - that's not in on-prem SQL Server.