Please note, perform steps below only if you are a DBA and know what the impact of this can be!


1. SQL Profiler
Start SQL profiler, create a new trace and select the template "Tuning". Choose to save the trace as a file. Click the second tab and uncheck all but TSQL section and start the profiler.
Let the profiler run until a representative snapshot of the activity has been made (eg walk through the site yourself, preferably in places where the hiccups are.
Stop the profiler

2. Database Engine Tuning Advisor
Start the Database Engine Tuning Advisor and start a new session.
Load the newly created file as workload, select the catalog you want and then start optimizing analysis.
Once the Advisor has finished, he gives what he can to optimize performance and what the profit percentages are.
On the Actions menu, click Apply Recommendations and go. He puts it on the basis of the workload exactly the indexing of that particular situation are best!

In specific cases we got SQL performance improvements over 90%!