From [Partial Aggregation](https://blogs.msdn.microsoft.com/craigfr/2008/01/18/partial-aggregation/):

> First, a partial hash aggregate requests only a fixed minimal memory grant as it presumes that it will be computing a relatively small number of groups.

 - Is the memory grant _always_ the same? i.e. is it always 1024kb (making a number up, here).

 - Is the memory grant derived from the query's total memory grant?

 - Is the memory grant a runtime decision based on how much memory is left over from other memory consuming operators?

I've noticed that, in repeated runs of the same query with no changes made, where it receives the same memory grant, different numbers of rows will emit from the Partial Aggregate, because:

> Second, a partial hash aggregate never spills rows to tempdb.  If a partial hash aggregate runs out of memory, it simply stops aggregating and begins returning non-aggregated rows.

Which in turn can have effects on other operators, where spills may be larger because fewer aggregated rows were emitted from the Partial Aggregate.

In this example, equal rows leave the Hash Match, but the Partial Aggregate emits slightly more rows in the top example. In the Global Aggregate all the way to the left -- the Hash Match -- both spill.

[![NUTS][1]][1]
     
But they spill slightly different amounts. Not a significant difference, but enough to make me wonder.

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

I'd spent some time using the MAX_GRANT_PERCENT hint to limit the amount of memory that the query gets overall, but that didn't seem to have a reliable impact on the Partial Aggregate.

The query looks like this:

```
SELECT TOP 1000
	   u.DisplayName,
	   MAX(u.Reputation) AS Reputation,
	   SUM(CASE WHEN p.PostTypeId = 1 THEN p.Score END ) AS QuestionsScore,
	   SUM(CASE WHEN p.PostTypeId = 2 THEN p.Score END ) AS AnswersScore 
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE u.DisplayName LIKE 'user[1-4]%'
AND EXISTS (SELECT 1 FROM dbo.Badges AS b WHERE b.UserId = p.OwnerUserId)
GROUP BY u.DisplayName
ORDER BY Reputation DESC;
```

[1]: https://i.imgur.com/OTj5e2X.png
[2]: https://i.imgur.com/jWG3SUr.jpg
Top Answer
Paul White
> * Is the memory grant *always* the same? i.e. is is always 1024kb (making a number up, here).

The minimal size for a hash table is 16KB (single partition, 512 row estimate).

It is possible that a local aggregate with many aggregations could get a larger fixed memory allocation, but I have not encountered this.

There is a separate instance of the local aggregate on each thread.

> * Is the memory grant derived from the query's total memory grant?

No it is acquired separately at query startup.

The *Memory Fractions* for a local aggregate are zero (input and output).

Grant-affecting options like `MAX_GRANT_PERCENT` have no effect.

> * Is the memory grant a runtime decision based on how much memory is left over from other memory consuming operators?

No.

> I've noticed that in repeated runs of the same query with no changes made, where it receives the same memory grant, different numbers of rows will emit from the Partial Aggregate

This depends on the order in which rows arrive, which will generally be different on each run due to timing differences in the plan operators below the local aggreate.

Each local aggregate adds a new entry to its hash table when it encounters a new grouping value. The entry maintains the local aggregate results associated with that key value. In your case, the key is `DisplayName`, and the maintained aggregations are:

```
[partialagg1011] = Scalar Operator(MAX([StackOverflow2013].[dbo].[Users].[Reputation]
[partialagg1012] = Scalar Operator(COUNT_BIG([Expr1007]))
[partialagg1014] = Scalar Operator(SUM([Expr1007]))
[partialagg1016] = Scalar Operator(COUNT_BIG([Expr1008]))
[partialagg1018] = Scalar Operator(SUM([Expr1008]))
```

As new rows arrive, they either contribute to the subtotals for an existing key, or add a new hash table entry. The number of unaggregated rows therefore depends on which group keys are encountered first. If key values with more duplicates are encountered early, fewer rows will be left unaggregated.

In this case, the subtotals are all fixed-length, but they do not have to be.  The hash table stores in-row data types directly. Longer length subtotals (e.g. strings) would take more space and reduce the number of total entries that would fit in the available space.

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.