Performance tuning is something of a black art. It's one of those things that before you can do it, you need to understand - really understand - what's going on, not only in your application, but in each of the platform components that makes up your application.
SQL Server has gotten easier to tune with every release. SQL Server 2005 has lots of features built in to make it easier to diagnose performance problems and to build better performing applications from the beginning. But SQL Server 2005 also has lots of architectural changes under the hood, which means that in order to dig deep, you need to understand those changes.
But Microsoft has recently "stealth released" a set of SQL Server Reporting Services reports that provide deeper and more actionable insight into the performance of your SQL Server than has ever been possible before without having access to a top-tier performance consultant. The SQL Server 2005 Performance Dashboard Reports report pack integrates directly into SQL Server Management Studio (using the new "Custom Reports" feature in SP2) and use realtime performance data from SQL Server's dynamic management views (DMVs) to identify poorly-performing queries and processes, show you the details on the problem, and (this is the big one) identify steps to take to resolve them. This often includes SQL DDL code to create indices, statistics, or whatever changes that can make your system run better.
Realistically, as a SQL Server consultant I'm not really afraid of being made obsolete by these amazing tools. There are some decisions that can only be made, and some problems that can only be solved by a truly deep knowledge of the platform. These reports are a major advance in moving the point at which the average SQL Server DBA needs to ask for help, and that means that the consultant is more likely to have a really interesting problem to solve when he is called in. And that can only be a good thing!