Hi Francis,

I did some research on this issue and found that it’s not a very common behavior of optimizer but we see this often with specific queries.

When optimizer compiles for a plan, various factors such as memory, CPU and cardinality are used to determine the most effective plan.

There have been a few cases where inefficient query plan results from large amount of memory, though it is very rare.

In these cases, what we experience is that if we reduce max server memory setting, the query runs faster.

So, the issue faced by you is very much similar and the point where its suddenly changing the plan is something we refer as “Tipping point”.

Recommendation:

=================

You can use trace flag 2335 to see if the problem is resolved.

This trace flag 2335 assumes SQL Server only has 1GB of memory available for query execution, and uses this memory setting to generate the query plan.

This will results in less memory intensive query plans such as nested loops.

Note: Setting this trace flag only affects optimizer for plan generation. It does not limit SQL Server from using all available memory for data buffers, query executions, etc.

We have a KB article http://support.microsoft.com/kb/2413549 documenting this.

Please let me know if this helps in fixing the issue.

Waiting for your findings so that we can move to next level of troubleshooting.

There is one more trace flag 4199 which is for implementing all optimizer fixes introduced so far.

Could you please implement both these trace flags in your SQL server startup parameter and then restart the SQL server services.

Please ensure that the trace flags are enabled from SQL server errorlog.

Let me know how this go.