My point was more that I feel it's a discrepancy that the DB tries to be very clever, but seemingly doesn't self-evaluate its decisions enough to determine when it wasn't.
Why should I have to guard against poor optimization decisions when the whole point is to just let the DB figure out the best way?
Because the point isn't that the DB should figure everything out, as it is a baseless claim that it's a 'poor optimization decision'. The point is to allow you to meet your requirements within your constraints. You're trading some of your constraints for different ones (here - you don't want to implement your own database, so you're using one that's already built with its own requirements and constraints). A database is a database, not a holy grail.
I haven't used MSSQL for some time now, but I'm sure there are tools which do exactly what you say you want, i.e. continuously monitor the DBMS and suggest actions or even act itself. This is usually done by a team of DBAs, though.
Why should I have to guard against poor optimization decisions when the whole point is to just let the DB figure out the best way?