May 28, 2015 at 10:12 am
Hugo Kornelis (5/28/2015)
robert.diley (5/28/2015)
I'd appreciate any more explanation of the distinction of Stream Aggregate v. Aggregate.Aggregate is a logical operator. It describes the process of applying aggregation function to data, (optionally) based on a column or set of columns to define groups. Because it is a logical operator, it describes WHAT is done, now HOW it is done.
Stread Aggregate is a physical operator - it is one of the (currently) two methods implemented in SQL Server to perform aggregation. This method requires that the data is sorted on the column(s) that define the groups. It works by reading rows one by one, keeping track of intermediate results in memory while discarding the actual rows, and then outputting a single row after the last row for a group has been read - at which time the internal counters are reset and the process continues for the next group.
The second physical operator used to implement Aggregate is Hash Match (Aggregate). This method uses an in-memory table that holds intermediate results for each distinct combination of values in the group by columns; these intermediate results are stored in memory at a location that is computed from a hash function (hence the name Hash Match). This method uses more memory than Stream Aggregate, but does not require that the input be sorted - and the output is also produced in a "random" order.
Hat off, Hugo - and thanks!
May 28, 2015 at 11:42 am
Hugo Kornelis (5/28/2015)
robert.diley (5/28/2015)
I'd appreciate any more explanation of the distinction of Stream Aggregate v. Aggregate.Aggregate is a logical operator. It describes the process of applying aggregation function to data, (optionally) based on a column or set of columns to define groups. Because it is a logical operator, it describes WHAT is done, now HOW it is done.
Stread Aggregate is a physical operator - it is one of the (currently) two methods implemented in SQL Server to perform aggregation. This method requires that the data is sorted on the column(s) that define the groups. It works by reading rows one by one, keeping track of intermediate results in memory while discarding the actual rows, and then outputting a single row after the last row for a group has been read - at which time the internal counters are reset and the process continues for the next group.
The second physical operator used to implement Aggregate is Hash Match (Aggregate). This method uses an in-memory table that holds intermediate results for each distinct combination of values in the group by columns; these intermediate results are stored in memory at a location that is computed from a hash function (hence the name Hash Match). This method uses more memory than Stream Aggregate, but does not require that the input be sorted - and the output is also produced in a "random" order.
Thanks. This is clear. What I am missing is the sense (context) in which Aggregate is a physical operator as would be required for it to be a correct answer.
May 28, 2015 at 11:53 am
robert.diley (5/28/2015)
What I am missing is the sense (context) in which Aggregate is a physical operator as would be required for it to be a correct answer.
I'm afraid I can't help you with that. It does say that it is a physical operator in Books Online, as can be seen from the link in the explanation of the question. But I disagree with that statement (as explained in my first post in this discussion).
I personally think that this may be an error in Books Online.
May 28, 2015 at 12:38 pm
This was removed by the editor as SPAM
May 28, 2015 at 4:20 pm
I have chosen AGGREGATE only as I have remembered a session in the SQL Server Days near Paris where the speaker told us that this operator was appearing when MIN , MAX , AVEARAGE, ... functions were used in a query. And this speaker explained us that it was a logical or physical operator on the contrary of STREAM AGGREGATE which was a physical operator.
Maybe , the question has not been written in an enough understandable way . Maybe also the BOL is in error ( why It has not been modified since the 2008 R2 version ? Usually , the SQL Server Documentation Team is correcting this kind of errors very quickly ).
I am admitting that to read a plan is not my usual task so I am not considering myself as a plan specialist but I don't think that STREAM AGGREGATE is the good choice ( even if some of yours have not seen the symbol AGGREGATE in your plans ).
I hope that nobody will be hurt by this post.
May 29, 2015 at 1:41 am
Nice question and even better discussion, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 29, 2015 at 9:30 am
Until I read Hugo's comment, I was going to comment that using buttons instead of check-boxes was wrong because there are two correct answers: aggregate and segment. But thinking a bit harder about it, I don't recall seeing aggregate as a physical operator, despite BoL stating clearly that it can be one. So I suspect BoL and todays question are both wrong and Hugo is right on this (but I'm not 100% sure).
Tom
May 30, 2015 at 5:54 pm
Hugo Kornelis (5/28/2015)
Okay, I am going to contest this one. I know that Books Online says that "Aggregate" can be "a logical operator or a physical operator". But who has ever seen an operator called "Aggregate" in an execution plan? Not me - I always see either "Stream Aggregate" or "Hash Match (Aggregate)", the two physical operators used to implement the logical "Aggregatet" operator. If anyone has ever seen an operator called "Aggregate" in an actual execution plan, I would like to know how to reproduce that!
If you have a look at the BOL about the operators , you will see that Aggregate is the only one operator with no defined icon. And I think that it is the reason why you have never met Aggregate in an execution plan , but I think that it is existing as operator.
I am sorry to contest your post especially because you brought me so important knowledge thru your blogs or your session in Paris and it is why I am in difficulty to contest your post.
May 31, 2015 at 2:00 am
patricklambin (5/30/2015)
Hugo Kornelis (5/28/2015)
Okay, I am going to contest this one. I know that Books Online says that "Aggregate" can be "a logical operator or a physical operator". But who has ever seen an operator called "Aggregate" in an execution plan? Not me - I always see either "Stream Aggregate" or "Hash Match (Aggregate)", the two physical operators used to implement the logical "Aggregatet" operator. If anyone has ever seen an operator called "Aggregate" in an actual execution plan, I would like to know how to reproduce that!If you have a look at the BOL about the operators , you will see that Aggregate is the only one operator with no defined icon. And I think that it is the reason why you have never met Aggregate in an execution plan , but I think that it is existing as operator.
I am sorry to contest your post especially because you brought me so important knowledge thru your blogs or your session in Paris and it is why I am in difficulty to contest your post.
Hi Patrick,
First of all (starting at the end): Never ever be sorry, or hesitate, to speak up if you think I am wrong. Nobody is right all the time. Everybody makes mistakes.
If you think I am wrong and point it out, there are two options. One is that I am indeed wrong, In that case, I learn something from your correction. The other option is that you are mistaken, or misinterpreted my words; in that case I can explain my point a bit better and you will learn something.
And in both cases, the silent large majority (I think I once read that every post in an internet forum represents about 20 people who think the same but don't want to post) learns.
In this case, I think you draw the wrong conclusion from the absence of an icon for the Aggregate operator.
If you look at the full list at https://msdn.microsoft.com/en-us/library/ms191158.aspx, you will see that there are more operators that have no icon. E.g. inner join, left anti join, right anti semi join (logical operators, to be implemented with one of the three physical join operators); branch repartition and segment repartition (logical operators to be replaced by the parallelism operator); distribute streams, gather streams, and repartition streams (logical operators to be implemented by a physical operator); or parallelism (a physical operator that can implement one of the three logical operators distribute streams, gather streams, or repartition streams).
What I conclude from that is that an icon is attached to either:
a) a physical operator if the SQL Server development team wants to use the same icon regardless of the logical operator implemented by it (e.g. nested loops, merge join, sort); or
b) a combination of physical + logical operator if the team wants different icons for the different logical operators (e.g. parallelism with different icons for distribute, gather, and redistribute streams).
For category b, you could argue that these icons are assigned to just the logical operator. I have not found examples of this category for logical operators that can be implemented with different physical operators.
So yes, you are right that I have not seen Aggregate in an execution plan because it has no icon. But I disagree with the conclusion that it still exists as a kind of "invisible" operator. I rather conclude from this that it doesn't have an icon because it is a logical operator only that has to be implemented with one of the two available physical operators, which eachh have their own icon - so no icon is needed for Aggregate because it only exists in intermediate versions of the plan during compilation and never in the end result displayed to the user.
BTW, I have also never seen an "Aggregate" physical operator in the XML of an execution plan, or in the textual version of a plan (SET SHOWPLAN_TEXT ON).
May 31, 2015 at 5:41 am
Thanks for your explanation that I think to have understood. But I am a slow man , so I think I will need some time to assimilate what you have written . You could understand why I am slow , search my name in a dictionary ( I found it the Harrap's ) , and I am really a lambin..
Thanks also for the time you spent to write your last post and have a good day...
June 1, 2015 at 11:43 am
Thanks.
June 1, 2015 at 11:43 am
Hi,
Ok, ok, I respect your feedback.
Regards.
June 1, 2015 at 11:55 am
Hi Tony,
Thanks for your comment.
I really want to pass some kind of information and knowledge.
The Aggregate operator, is part of a large collection of operations used by SQL Server during the processing of a query or script. This operator by default is used whenever an aggregation operation values, among them: SUM, MAX, Min, etc, is used.
When querying the same execution plan is presented as Aggregate or Aggregate Streem, depending on the way the execution plan recognizes him.
This example can illustrate the use of this operator:
Select TaxType, count (TaxType) As ' Count '
From Sales SalesTaxRate Rows.
Group By TaxType
Hugs.
June 2, 2015 at 7:16 am
Thanks for the question.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply