From MS

"SQL server will choose a plan with less cost, in your case SQL choses merge join plan as its cost it less than hash join... this is by design....

The "fix" for this was enabling trace flag 2335

DBCC TRACEON (2335, -1)

More notes from...

Here is what I've gotten from Microsoft.

Looking through the plan I see that there is a difference in how SQL is generating the query plan for the same query under...

If the OS were running out of resources it would take 30 seconds to run the query when I had 48 GB installed and 48 GB allocated to SQL. It's...

Another interesting bit of information:

(Physical RAM Config) SQL Allocation - Time it took

(6x8GB) 48GB allocated to SQL - Query takes 6 seconds

(12x8GB) 84 GB allocated to SQL – Query takes...

Derrick Smith (9/10/2010)

---

Maxdop 0 is generally a bad setting regardless. If you have 24 logical CPUs, I would force maxdop 8 on the server.

What is the total ram on the...

Ran a test while tracking swap file disk usage.

SQL set to use 84 GB max memory

6 seconds for the query

SQL set to use 92 GB max memory

30 seconds for the...

ALZDBA (9/10/2010)

---

Indeed, setting max server memory is a must on 64-bit systems !

If only I would have noted the source ref in my script :blush:

--/* These settings are for x64,...

Interesting note, if we change the max dop to 8 from 0 we can set RAM back to 2 TB and get that query down to 5-6 secs.

ALZDBA (9/10/2010)

---

There has be a similar Q last week.

They pointed to a bug in W2008R2 and proposed the following hotfix a the solution: Win2008R2_Win7_HotFix_KB2265716 http://support.microsoft.com/kb/2265716

Does this apply to...