showplan tells you the final decisions that the optimizer makes about your queries. dbcc traceon(302) can often help you understand why the optimizer makes choices that seem incorrect. It can help you debug queries and decide whether to use certain options, like specifying an index or a join order for a particular query. It can also help you choose better indexes for your tables.
When you turn on dbcc traceon(302), you eavesdrop on the optimizer as it examines query clauses and applies statistics for tables, search arguments, and join columns.
The output from this trace facility is more detailed than showplan and statistics io output, but it provides information about why the optimizer made certain query plan decisions.
The query cost statistics printed by dbcc traceon(302) can help to explain, for example, why a table scan is chosen rather than an indexed access, why index1 is chosen rather than index2, and so on.
