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:
(Click image for full size)
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:
(Click image for full size)
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.
» 
IE 7
Firefox 2.0
Comment and Contribute
