meme
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