Show Execution Plan
A decent adjunct to the Profiler is the graphical Execution Plan available in the query analyzer. Just select the Query Menu and near the bottom is "Show Execution Plan". Select it and run a query or stored procedure and the execution plan will be shown graphically. You can also show the execution plan without running the query by selecting "Display Estimated Execution Plan" under Query Menu (or Ctl-L).
The information shown is all you need to know to attack an optimization problem. It tells what the execution strategy is and the relative cost of each piece. Optimization will consist of either speeding up individual parts, most likely with indexes, or making the database engine choose a different execution strategy altogether by reformatting the query or rearchitecting the data.
If more than one statement is executed, as in a batch or procedure, or inside a trigger, they will all be graphed and the relative costs will be displayed:
Reading the output can be something of an art, especially for complex queries involving many tables where the picture is so large it's difficult to see many of the pieces at once. It's not truly necessary to know what all the icons and terminology mean, but only to understand how they relate to the query and point to the slow operations. A gut feel will suffice.
To optimize a query, you should check each piece for its relative cost. Find the one or two spots with a high cost. In the above picture there is one piece that takes 21% and everything else is negligible. When optimizing a query you can usually just concentrate on minimizing the most expensive parts, while ignoring the rest.
If you hover the mouse over an icon, you can see its details:
Common things to look for are using an unexpected index and getting a large rowcount when you believe the query should be easily restricted on this table.
There's also a text based execution plan you can use, but I prefer the graphical output.