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

GokhanVarol 2013-07-16 20:19:59

I would expect this query having no memory grant. "WhyMemoryGrant.sql" is the sql ran, "WhyMemoryGrant.sqlplan" is the estimated plan that query started running under, if ran "WhyMemoryGrantData.sql" it would show detailed data about the session collected
Thank you

alt text

alt text

SQLkiwi 2013-07-19 00:49:07
The memory grant is used by the nested loops join. When the "optimized" property of a nested loops join is set:

Optional Optimized Property

…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.