AVOID merge joins

Hi,
Does anyone know of a way to AVOID merge joins?
I have a complex query that works fine on one system, but not on another.
After inspection, I found that the difference lies in different query plans generated by the query optimizer. On the failing system, a merge join is used, but since the total size of the columns is more than 8k, I get the error:
Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8571, which is greater than the allowable maximum of 8094.
With query optimizer hints I can favour an optimization, but is there also a way to AVOID one?
(It should be a query optimizer hint on the complete SELECT, not on a specific table: the merge join is used on an intermediate step of the query, not on an identified table.)
Many thanksIn the mean time, I have found that I can use OPTION(ROBUST PLAN) in the select. Does anyone know the syntax when I would want to use it in a view?

Thanks,
Jan|||Basically it seems an issue that you have is not with type of join but with row size. When you use ROBUST PLAN option optimizer will try to use the max possible row size at the expense of actual query performance.
I would recommend to review query strategy to avoid of using huge row sizes.

But in a mean time you may try to use join type hint to prevent of using MERGE JOIN or use OPTION with particular JOIN type.

Regards.

AUTOSTATS on tempdb

Hello,

I've found this recomendation recently:

"To help speed queries or joins on large temp tables, be sure the AUTOSTATS database option is turned on for tempdb"

How can I find out what this option set for our tempdb? If I go to "database options" of tempdb under "System databases", I do not see that option at all.

Please help. Thanks

Do you see 'Auto Create Statistics'?

Alternatively you can run sp_dboption on tempdb to see what the settings are:

exec sp_dboption 'tempdb'

You should see that 'auto create statistics' is set

Don

|||

run this query

select DATABASEPROPERTY( 'tempdb ', 'IsAutoCreateStatistics' )

1 = TRUE

0 = FALSE

NULL = Invalid input

Madhu