or
Erik Darling
sql-server sql-server-2017
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.
How Do Partial Aggregates Calculate Their Memory Grants?
Erik Darling
nice answer
Paul White
It's a serial local aggregate
Paul White
https://www.brentozar.com/pastetheplan/?id=SyovK8EhH
Erik Darling
you're an insight
Paul White
Well that's an insight
Erik Darling
because there's nothing to partially aggregate
Erik Darling
i don't think there are partial aggregates in serial plans
Erik Darling
but like
Paul White replying to Erik Darling
insight
Erik Darling
what was i supposed to get from the maxdop 1 plan?
Erik Darling
ahem
Erik Darling
how do i get to a real chat room
Erik Darling
but doesn't update the question
Erik Darling
every time i hit the update button, it injects this: `![](/image?hash=)` 
Erik Darling
also 
Paul White
I still had it open in Plan Explorer
Erik Darling
dammit
Erik Darling
but it's not showing there after i hit update
Erik Darling
yeah
Paul White
right?
Paul White
```sql  
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 @DisplayName
   AND EXISTS (SELECT 1 FROM dbo.Badges AS b WHERE b.UserId = p.OwnerUserId)
   GROUP BY u.DisplayName
   ORDER BY Reputation DESC;  
```
Erik Darling
dammit
Paul White
No it doesn't appear for me either
Erik Darling
i've tried updating the question several times but i'm not seeing it in there
Paul White replying to Erik Darling
No, I guess I *need to refresh*
Paul White
Fair
Erik Darling
you didn't give me one when i wrote a bad bitmap demo at 6:40 :P
Erik Darling
are you seeing the query in the question?
Paul White
:)
Paul White
Give me a break
Paul White
Hey it's 6:40 am here
Erik Darling
yay?
Paul White
Also I finally recognised the query
Paul White
Oh I see
Erik Darling
yes, for the inner join
Paul White replying to Erik Darling
On each thread? That seems impossible? One shows 200,076 total, the other 199,211.
Erik Darling
Row counts are the same at the hash match (at least for me)
Paul White replying to Erik Darling
Yes entering the partial aggregate, comparing the two plans.  
It might be tricky to determine which thread is hitting its limit, hence the suggestion to run `MAXDOP 1`
Erik Darling
@Paul per thread on the partial aggregate or somewhere else?
Paul White
@Erik Also, could you add the query to the question please.
Paul White
@Erik Did you check the per-thread distribution?  
 Is the effect clearer in a serial plan?  
 My recollection is that partial agg uses by 512KB but I need to check.