It's a very powerful feature in MS SQL Server Management Studio, but I've always been lost when it comes to understanding what it's trying to tell me about a query.
Edit: expanded list to included covering vs noncovering indexes
Do you mean the execution plan?
The plan shows how SQL Server will execute your query. The main benefit is to see what indexes are used (or not used). Here's a basic list (best to worst) of some methods used to find data:
The actual performance of a covering index scan would fall somewhere after a covering index seek but before any noncovering index scan. It would depend on the table size, index sizes, index column data types, etc. The difference between seeks and scans increases as the number of rows in the table/index increases.
Side note: In SQL Server 2008, you can create filtered indexes that can also improve performance by decreasing the number of rows in the index.
If you see the plan using one of the later methods, see if perhaps your query can be changed to use a better index. It's also possible that you might need to create a new index to improve performance.
Sorry. It's been a long day, and I'm tired, so that's all I've got right now.
If you want more information, here's a good review I just found: http://www.sqlservercentral.com/articles/Administration/executionplans/1345/
To read a plan, start with the window closed. Work out how you'd solve the problem in an ideal, but paper-based, world.
Then go to the plan, start at the top row, on the right, and work in. Look at the size of the arrows, and see how the system is solving your query.
Learn about the different join methods (merge,hash,loop) to know to interpret branches. Also, whenever you see a COMPUTE SCALAR, look inside to see if it's calling a function. If it is, then watch out because the effort to work out the value of that function isn't included in the cost of your query.
Make sure you understand about Lookups. If the plan uses a non-clustered index (NCIX), but the NCIX doesn't have all the data you need, it will need to do a Lookup to get the rest. This is like a CIX Seek for each row. Look at the size of the arrow to see how many rows it needed to do. You may well find that a Scan over the whole table would've been better.
Could go on for hours (or pages), but this will be a useful start. Make sure you think about how you'd solve the query, and then look at the plan to see if the Query Optimizer agreed with you.
This is a huge topic. Here are some high points that I always try to emphasize:
Remember that there are two types of execution plans, estimated and actual. They're usually the same, but not always. The data supplied between the two is different in a few important points, the number of executions for a given operator, the actual number of rows moved, and the actual cpu cost for the operator.
Read graphical execution plans from the top right, working backwards.
The cost estimates on display for each of the operators are just that, estimates. They're estimates in the actual plan too. They are not reliable as a means for measuring performance differences between two plans, but they're one of the few measurements available.
The thicker the connecting arrow between two operators, the more data that is being moved.
Performance points to look for. None of these are carved in stone and there are always exceptions:
Check the properties on the final (first listed) operator to see if the execution plan is the best plan found or the result of an optimizer timeout. If it's a timeout, you may have tuning or tweaking opportunities that are not readily apparent. You also may need to apply hints to arrive at an optimal plan.
Toms answer covers a lot of it and I just want to add some points. Take a look at the estimated or actual cost of the steps, number of executes and the cost in percent. Even if cost is not an exact number, it will give you something to compare different solutions with. And with the percentage you can pinpoint the heavy parts of your query.
No one has followed this question yet.