"premature optimization is the root of all evil"
In terms of database programming, I think this quote is nonsense. It is extremely expensive to re-write your whole application because your developers don't care to write efficient code the first time. All t-sql code should be thought of in terms of how it will affect database performance second (data integrity is, of course, first). Perfomance should trump everything except data integrity.
Yes, there are optimization things you shouldn't do until you have issues, but some things should be done as a matter of course and not fixed later. It takes no more time to write code that has a better chance of being efficient than code which will not be once you understand how you are affecting efficiency with the bad code. Cervo's discussion of cursor code is one example. Set-based actions are almost always much faster than cursor solutions, so cursors should not ever be written initially when a set-based solution will do. It almost always takes me less time to write a set-based solution that it would to write a cursor, but the only way to get that way is to never write cursors.
And there is no reason to ever use select * instead of specifying your field names. In MSSQL you can drag those names over from the object explorer so you can't tell me it's too hard to do that. But by specyfying only the fields you actually need, you save network resources and database server resources and web server resources. So why should a programmer ever take the lazy option of select * and worry about optimizing later?
The same thing with indexes. You say you do a minimal set of indexes. Depending on how you define minimal, that could be ok, but it is critical to have indexes on all foreign keys and I wouldn't want to push a database that didn't have indexes on a few fields that are most often in the where clauses. If your users are outside clients and not internal, they won't complain about how slow your site is, they will go elsewhere. It only makes busness sense to plan for efficient database access from the start.
One of my main concerns about failing to consider efficiency from the beginning is that the first couple of times that things are too slow companies tend to just throw more equipment at the issue rather than performance tune. By the time people start performacne tuning you have a several gigabyte or more database with many unhappy customers who are getting timeouts more than results. At this point, often almost everything in the database has to be re-written and in the meantime you are losing customers. I remember providing support at one company with a commercial application that it literally took ten minutes for the customer service reps to move from one screen to another while they were trying to help already disgruntled customers on the phone. You can imagine how many customers the company lost due to poorly designed database queries in the commercial product that we could not change.