In an ideal world a database engine would self heal and self tune, and the SQL Server product team work long and hard on each release to try and realise this. However in parallel with this they recognise that they can’t cater for every eventuality and so have provided more and more diagnostics and tools for the DBA to do tuning themselves.
I know about some of this stuff so I would always defer to the experts like the Premier Field Engineers and the support team we have (GTSC). There’s also a lot of expertise out there among the SQL Server community and partners so you shouldn’t ever be stuck with a problem. However if you would rather not get a man in to fix & tune your application issues or you work for a partner then you could decide to skill up in this area.
A good place to start is this excellent 3 day level 400 Performance Monitoring & Tuning Workshop run by Ramesh Meyyappan, one of the highest scoring speakers at the recent SQL Server 2008 R2 Tech Day and the last SQL Bits. It’s in London June 22-24 and you can register for it here.