Azure's SQL database feature for auto-tuning creates and drops indexes based on database usage. I've imported an old database into Azure which did not have comprehensive indexes defined and it seems to of done a great job on reducing CPU & DTU usage over a relatively short period of time.
It feels wrong - but does this mean I can develop going forwards without defining indexes? Does anyone do this? SSMS index editor is a pain and slow to modify with. Not having to worry/maintain indexes would speed up development time.
The auto-tuning is taking advantage of three things, Query Store, Missing Indexes and Machine Learning.
First, the last known good plan is a weaponization of the Query Store. This is in both Azure and SQL Server 2017. It will spot queries that have degraded performance after a plan change (and quite a few executions, not just one) and will revert back to that plan. If performance degrades, it turns that off. This is great. However, if you write crap code or have bad data structures or out of date statistics, it doesn't help very much.
The automatic indexes in Azure are using two things, missing index suggestions from the optimizer and machine learning on Azure. With those, if the missing index comes up a lot over a period of 12-18 hours (read this blog post on automating it), you'll get an index suggestion. It measures for another 12-18 hours and if that index helped, it stays, if not, it goes. This is also great. However, it suffers from two problems. First, same as before, if you have crap code, etc., this will only really help at the margins. Second, the missing index suggestions from the optimizer are not always the best index. For example, when I wrote the blog post, it identified a missing index appropriately, but it missed the fact that an INCLUDE column would have been even better than the index it suggested.
A human brain and eyeball is still going to be solving the more difficult problems. These automations take away a lot of the easier, low-hanging problems. Overall, that's a great thing. However, don't confuse it with a panacea for all things performance related. It's absolutely not.