SQL Server Performance tuning

http://msdn.microsoft.com/en-us/library/ms178100%28v=sql.100%29.aspx

Database Engine Tuning Advisor

http://msdn.microsoft.com/en-us/library/ms166575%28v=sql.100%29.aspx

The Database Engine Tuning Advisor enables you to tune databases for improved query processing. Database Engine Tuning Advisor examines how queries are processed in the databases you specify and then it recommends how you can improve query processing performance by modifying physical design structures such as indexes, indexed views, and partitioning.

It replaces the Index Tuning Wizard from Microsoft SQL Server 2000, and offers many new features. For example, Database Engine Tuning Advisor provides two user interfaces: a graphical user interface (GUI) and the dta command prompt utility. The GUI makes it easy to quickly view the results of tuning sessions, and the dta utility makes it easy to incorporate Database Engine Tuning Advisor functionality into scripts for automated tuning. In addition, Database Engine Tuning Advisor can take XML input, which offers more control over the tuning process. For more information about Database Engine Tuning Advisor new features, see Database Engine Tuning Advisor Features.