The SQL Server Database Tuning Adviser will happily create all the indexes, stats, and what not, for a given workload. And yeah, you can speed up your queries (the workload) as a result. The problem is that there's always a trade off - those queries may now be fast, but inserts and updates end up being slow due to the burden of index maintenance.

There's also maintenance views that can be queried to see if SQL Server is detecting any missing indexes, or if there's any indexes that are not being used - but again these have to be taken with a grain of salt, as the stats get reset each time those indexes are rebuilt.

> Did they build the right indexes? Well... the DB doesn't know.

The DB does in fact know - looking at the query plan for a given query will outline if there are any obvious missing indexes - but having the DB go ahead and build indexes by default for every given query is dangerous. When you go through 10 iterations of an ad-hoc query refining it down to what you actually want, how's the DB to know that the first 9 don't count? Etc..

> You should say "I want to run these queries, and I have this much memory for you" and it should know how to get your results as fast as possible.

That is in essence what the query optimizer attempts to do, though it has breaking points where it will settle for 'good enough' because optimization of the query itself becomes too expensive. Sometimes people just write bad queries.