tsql inner merge join

we've got some simple sql-queries, joining several tables with inner joins. indices are existin开发者_C百科g, and no missing indices are suggested by optimizer.

the queries run fine on our test-environment and several other systems. we've applied the sql to another system, and the query breaks down. runtime on own server: nearly instant. runtime on new server: about 40 seconds.

the amount of data is matchable.

adding the "merge" hint to the inner join gives the estimated runtimes, known from the own server.

why those differences?

we're on ms-sql 2008 r2.

Maybe parallel execution settings and evironment are different on the "slow" system.

Hinting the "merge join" probably you replaced a loop join.

The best way to be sure is to get the query plans generated in the two different systems, and look at differences.