Optimizer Logic Tracing Tool

Guys,
what I need is a tool which gives details on the choice of an
execution plan by the SQL Server. For example, the cost for a hash
join might be 200 and 100 for a nested loop, and therefore a nested
loop is used. Same thing for the access paths for each table/view
involved. In Oracle, we turn on event 100053 to see this kind of info.

Thanx

Daniel"Daniel Roy" <danielroy10junk@.hotmail.com> wrote in message
news:3722db.0401160711.7b1d34b4@.posting.google.com ...
> Guys,
> what I need is a tool which gives details on the choice of an
> execution plan by the SQL Server. For example, the cost for a hash
> join might be 200 and 100 for a nested loop, and therefore a nested
> loop is used. Same thing for the access paths for each table/view
> involved. In Oracle, we turn on event 100053 to see this kind of info.
> Thanx
> Daniel

There's no real equivalent in MSSQL - you can see the plan that optimizer
selected, not the logic it used to arrive at that plan. Query Analyzer can
display the cost of each step in the plan, and how many rows were affected,
either after the query runs or before (by asking for an estimated plan).
This is more or less a graphical presentation of the SET SHOWPLAN_ALL ON
output.

Simon|||> There's no real equivalent in MSSQL - you can see the plan that optimizer
> selected, not the logic it used to arrive at that plan. Query Analyzer can
> display the cost of each step in the plan, and how many rows were affected,
> either after the query runs or before (by asking for an estimated plan).
> This is more or less a graphical presentation of the SET SHOWPLAN_ALL ON
> output.

Isn't there ANY other way to understand the optimizer's logic in
choosing a specific execution plan as opposed to another one?

Daniel