Optimizer Logic Tracing Tool

[Daniel Roy]

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

[Simon Hayes]

"Daniel Roy" <da*************@hotmail.com> wrote in message
news:37************************@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

[Daniel Roy]

> 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