If the query optimizer doesn’t choose the query plan you’d expect, and basically would expect tables to be joined in a different order than you’d have expected, you need to get more details about what the optimizer has considered and why it’s chosen a given query plan.
First to see what the optimizer chooses for a given query, you should execute the following before executing the query:
set showplan on
If your query is causing a huge load, you should consider executing it with the noexec option. This will cause the optimizer to show you the query plan, it would use but not execute it:
set showplan on go set noexec on go
After executing the query, you can set back both options:
set noexec off go set showplan off go
The order is important since you cannot set showplan back to off while noexec is on.
This will also show you, whether you are performing any table scans and what the expected total cost for the query is.
Now you know which tables are processed in which order and how the joins are performed. Very often, you get a different order on two systems depending on the number of rows, distribution of data, how exact the statistics are… It might also use different indexes on different systems.
If what you’re after is why a given index has been chosen for a table, you should execute the following (before setting the noexec option):
dbcc traceon(3604, 302)
3604 makes sure that the trace output is sent to the client preventing it from filling up your error log.
302 prints information regarding which indexes were considered and why a given index was chosen.
You should check in the output of dbcc trace 302 that all where clauses in the query have been evaluated. If it is not the case the optimizer considered it as not being a valid search argument. This will give you a first possible cause of the problem.
Now that you know which indexes were chosen and why, you will often also want to know why the tables were joined in a particular order. For this you should use the following:
dbcc traceon(3604, 310)
It will show you the number of tables in join and the number of tables considered at a time. It then prints the first plan considered by the optimizer, and then each plan which is considered cheaper (so iteratively showing you the currently selected plan during the analysis). It writes the heading “NEW PLAN” for each of these plans (it’s important if you combine 310 with 317 which displays the rejected plans with another heading). It also prints information about the number of rows expected as input and output of a join, the costs involved (as well expected logical and physical IO).
If the plan you’d have expected to be chosen is in the list, you’re done here. You see why the optimizer tought the other plan is better and can start looking for a way to convince the optimizer that the other plan would have been better. Often, the optimizer is not just wrong but has wrong input data (statistics…).
If the plan you’d have expected is not there, it’s one of the plans which was rejected because the optimizer thought it already had a better plan. So you need to display also rejected/ignored plans:
dbcc traceon(3604, 310, 317)
The 317 trace basically displays the same info but for not selected plans. It uses the heading “WORK PLAN” instead of “NEW PLAN”. This produces a very large output (can easily be 20 MB of text).
If you cannot find any better way to have the optimizer think the way you want it to think, you can use forceplan:
There is a way to force the optimizer to choose a certain plan. Specifically, the join order specified by the query itself, in the “from” clause.
set forceplan on
This forces the optimizer to use the exact join order defined in the list of tables in the FROM clause of the query. But this is only useful if you have an influence on the order of table in the FROM clause. I usually do not like using forceplan but sometimes it’s the only solution you have (especially if you don’t have endless time to solve a performance issue).
Please note that it only tells Sybase which join order to use, not which join types.