Many of the tools, for example, the set commands, affect the decisions made by the optimizer. showplan and dbcc traceon(302, 310) show you optimizer decision-making. dbcc traceon(302,310) shows intermediate information as analysis is performed, with dbcc traceon(310) printing the final plan statistics. showplan shows the final decision on access methods and join order.
statistics io and statistics time provide information about how the query was executed: statistics time measures time from the parse step until the query completes. statistics io prints actual I/O performed during query execution.
noexec allows you to obtain information such as showplan or dbcc traceon(302,310) output without actually executing the query.
