Why is this serial query with only merge join and nested loops getting max memory grant?

…the execution engine uses memory to perform an opportunistic batch sort (not a full sort) on the join key(s) for rows on the outer input. The idea is to promote a sequential access pattern on the inner side of the join. You can disable this on a per-query basis using trace flag 2340:

http://support.microsoft.com/kb/2009160

The issue is also described briefly in:

http://blogs.msdn.com/b/psssql/archive/2010/01/11/high-cpu-after-upgrading-to-sql-server-2005-from-2000-due.aspx (not a case of excessive memory use, but same core issue)

The memory grant you are seeing is certainly excessive though (batch sort would use just a few tens of MB in this case, and never anything like the 0.6TB 'ideal' shown). You have a server with lots of memory, so you may also be encountering the following 'bug':

http://support.microsoft.com/kb/2413549/en-us

That KB article describes trace flag 2335 as a start-up only option, which may make it unsuitable for your workload. I haven't tried flag 2335 to see if it is possible to enable it on a per-query basis using DBCC TRACEON or OPTION (QUERYTRACEON). Give that a go.

In summary, the cause is batch sort for optimized nested loops join, but the algorithm certainly seems to reserve far too much memory when a large number of rows is combined with a server with a very large amount of memory. If 2335 does not work for you, and it is not practical to use 2340, contact CSS to report this as a bug.