SQL Server 2005 comes with a built-in answer to this problem: the Database Engine Tuning Advisor. Combining a simple user interface with a deep knowledge of SQL Server, this utility can help you tune your databases for peak performance.
Microsoft’s reference and tutorial pages for the Database Engine Tuning Advisor can be viewed at the following links:
http://msdn2.microsoft.com/en-us/library/ms173494.aspx
http://msdn2.microsoft.com/en-US/ms166575.aspx