sql-server add tag
meme (imported from SE)
I've heard conflicting things about memory grants for parallel select queries:

- Memory grants get multiplied by DOP
- Memory grants get divided by DOP

Which is it? 
Top Answer
meme (imported from SE)
Sup?
--
For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads.

Memory grant estimates are based on:

 - Number of rows (cardinality)
 - Size of rows (data size)
 - Number of concurrent memory consuming operators
 
If a parallel plan is chosen, there is some memory overhead to process parallel exchanges (distribute, redistribute, and gather streams), however their memory needs are still not calculated the same way.

Memory Consuming Operators
--
The most common operators that ask for memory are

 - Sorts
 - Hashes (joins, aggregates)
 - Optimized Nested Loops

Less common operators that require memory are inserts to column store indexes. These also differ in that memory grants are currently multiplied by DOP for them.

Memory needs for Sorts are typically much higher than for hashes. Sorts will ask for at least _estimated size of data_ for a memory grant, since they need to sort all result columns by the ordering element(s). Hashes need memory to build a hash table, which does not include all selected columns.

Examples
--

If I run this query, intentionally hinted to DOP 1, it will ask for 166 MB of memory.

    SELECT *
    FROM 
         (  
    	    SELECT TOP (1000) 
    	           u.Id 
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u
    OPTION(MAXDOP 1);

[![NUTS][1]][1]

If I run this query (again, DOP 1), the plan will change, and the memory grant will go up slightly.

    SELECT *
    FROM (  
    	    SELECT TOP (1000) 
    	           u.Id
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u
    JOIN (
    		SELECT TOP (1000) 
    		       u.Id
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u2
    ON u.Id = u2.Id
    OPTION(MAXDOP 1);

[![NUTS][2]][2]

There are two Sorts, and now a Hash Join. The memory grant bumps up a little bit to accommodate the hash build, but it does not double because the Sort operators cannot run concurrently. 

If I change the query to force a nested loops join, the grant will double to deal with the concurrent Sorts.

    SELECT *
    FROM (  
    	    SELECT TOP (1000) 
    	           u.Id
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u
    INNER LOOP JOIN ( --Force the loop join
    		SELECT TOP (1000) 
    		       u.Id
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u2
    ON u.Id = u2.Id
    OPTION(MAXDOP 1);


[![NUTS][3]][3]

The memory grant doubles because Nested Loop is not a blocking operator, and Hash Join is.

Size Of Data Matters
--
This query selects string data of different combinations. Depending on which columns I select, the size of the memory grant will go up.

The way size of data is calculated for variable string data is rows * 50% of the column's declared length. This is true for VARCHAR and NVARCHAR, though NVARCHAR columns are doubled since they store double-byte characters. This does change in some cases with the new CE, but details aren't documented.

Size of data also matters for hash operations, but not to the same degree that it does for Sorts.


    SELECT *
    FROM 
         (  
    	    SELECT TOP (1000) 
    	             u.Id          -- 166MB (INT)
                   , u.DisplayName -- 300MB (NVARCHAR 40)
                   , u.WebsiteUrl  -- 900MB (NVARCHAR 200)
                   , u.Location    -- 1.2GB (NVARCHAR 100)
                   , u.AboutMe     -- 9GB   (NVARCHAR MAX)
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u
    OPTION(MAXDOP 1);

But What About Parallelism?
--

If I run this query at different DOPs, the memory grant is not multiplied by DOP.

    SELECT *
    FROM (  
    	    SELECT TOP (1000) 
    	           u.Id
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u
    INNER HASH JOIN (
    		SELECT TOP (1000) 
    		       u.Id
    		FROM dbo.Users AS u
    		ORDER BY u.Reputation
    	 ) AS u2
    ON u.Id = u2.Id
    ORDER BY u.Id, u2.Id -- Add an ORDER BY
    OPTION(MAXDOP ?);

[![NUTS][4]][4]

There are slight increases to deal with more parallel buffers per exchange operator, and perhaps there are internal reasons that the Sort and Hash builds require extra memory to deal with higher DOP, but it's clearly not a multiplying factor.


  [1]: https://i.stack.imgur.com/gOzLn.png
  [2]: https://i.stack.imgur.com/07lPf.png
  [3]: https://i.stack.imgur.com/kiBHV.png
  [4]: https://i.stack.imgur.com/mf1jY.png

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.