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.