May 27, 2015 at 10:27 pm
Comments posted to this topic are about the item Operator Description
May 27, 2015 at 10:28 pm
Nice question
May 28, 2015 at 2:38 am
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!
On the other hand, the option "Segment" should be marked as correct. A Segment operator is often used in conjunction with aggregations. Not in the simplest queries (simple aggregation with a single GROUP BY). But if you add some complexity, like e.g. an OVER() clause, or when you do aggregation in a correlated subquery, you will almost always get Segment operators in the plan. So it definitely "can be recognized" in the plan when using aggregate functions. And it most definitely is described as "a logical operator or a physical operator" on the page referenced by the question's explanation.
Please change the question to have "Segment" marked as the correct answer and "Aggregate" as incorrect.
May 28, 2015 at 3:25 am
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!On the other hand, the option "Segment" should be marked as correct. A Segment operator is often used in conjunction with aggregations. Not in the simplest queries (simple aggregation with a single GROUP BY). But if you add some complexity, like e.g. an OVER() clause, or when you do aggregation in a correlated subquery, you will almost always get Segment operators in the plan. So it definitely "can be recognized" in the plan when using aggregate functions. And it most definitely is described as "a logical operator or a physical operator" on the page referenced by the question's explanation.
Please change the question to have "Segment" marked as the correct answer and "Aggregate" as incorrect.
I chose Segment because BOL states that it is a physical and a logical operator which is what the question asks. Aggregate was described as a physical or a logical operator. I saw no reason why Segment could not be used with the aggregate functions and chose that. There is a difference between AND and OR.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
May 28, 2015 at 5:29 am
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!
I have to agree, Hugo. I don't recall ever seeing "Aggregate" in a plan. I see "Stream Aggregate" with an "Aggregate" logical operation.
May 28, 2015 at 6:12 am
I actually got this right after thinking about it, but I think Hugo is accurate in what he's saying.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2015 at 7:18 am
+1
Gerald Britton, Pluralsight courses
May 28, 2015 at 7:19 am
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!On the other hand, the option "Segment" should be marked as correct. A Segment operator is often used in conjunction with aggregations. Not in the simplest queries (simple aggregation with a single GROUP BY). But if you add some complexity, like e.g. an OVER() clause, or when you do aggregation in a correlated subquery, you will almost always get Segment operators in the plan. So it definitely "can be recognized" in the plan when using aggregate functions. And it most definitely is described as "a logical operator or a physical operator" on the page referenced by the question's explanation.
Please change the question to have "Segment" marked as the correct answer and "Aggregate" as incorrect.
+1
Gerald Britton, Pluralsight courses
May 28, 2015 at 7:46 am
I think the correct answer would be "Stream Aggregate". Anyways.!!
Thanks.
May 28, 2015 at 7:54 am
Beyond the question of which answer is right, I want to know why I care. The poster of this QOTD must have thought there was something interesting or helpful to know here, but the answer doesn't give any explanation on how this knowledge applies to my work.
Can anyone here explain?
May 28, 2015 at 7:59 am
SQL-DBA-01 (5/28/2015)
I think the correct answer would be "Stream Aggregate". Anyways.!!
Stream Aggregate is a physical, but not a logical operator. Hence it is not the correct answer.
May 28, 2015 at 8:02 am
Tony++ (5/28/2015)
Beyond the question of which answer is right, I want to know why I care. The poster of this QOTD must have thought there was something interesting or helpful to know here, but the answer doesn't give any explanation on how this knowledge applies to my work.Can anyone here explain?
A full understanding of execution plans can be very helpful for tuning slow queries.
This question does touch on a two important subjects:
1. The distinction between logical (what they do) and physical (how they do it) operators.
2. Understanding which operators can play a role in plans for queries that involve aggregation, and which don't (and in what other queries they might play a role).
The explanation of the question does not go into details on this, but that is very often the case with the QotD. (And, honestly, a full explanation would be very long - I can teach for a whole day on execution plans and still leave a lot of stuff uncovered.) But you can use it as a starting point for further reading/investigation.
May 28, 2015 at 9:19 am
I'd appreciate any more explanation of the distinction of Stream Aggregate v. Aggregate. Frankly, I don't recall ever seeing Aggregate on its own. I opted for Stream Aggregate on the grounds that 1) I recalled seeing the icon in graphical representations of query plans and 2) Stream Aggregate appears to subsume Aggregate, the former listed as a physical operation and the latter as logical. I would be interested if someone could assist in producing Aggregate as a physical operator....
May 28, 2015 at 9:20 am
This is what MSDN says about it:
https://msdn.microsoft.com/en-us/library/ms190472(v=sql.105).aspx
Edit: I forgot something... Thanks for the question, Junior!
May 28, 2015 at 9:57 am
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.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply