Operator Description

  • Junior Galvão - MVP

    SSCertifiable

    Points: 6426

    Comments posted to this topic are about the item Operator Description

  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    Nice question

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Neil Burton

    SSC-Insane

    Points: 21999

    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.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Ed Wagner

    SSC Guru

    Points: 286960

    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.

  • Grant Fritchey

    SSC Guru

    Points: 395634

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • g.britton

    SSChampion

    Points: 13686

    +1

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • g.britton

    SSChampion

    Points: 13686

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • SQL_Hunt

    SSC-Dedicated

    Points: 33267

    I think the correct answer would be "Stream Aggregate". Anyways.!!

    Thanks.

  • Tony++

    SSCarpal Tunnel

    Points: 4281

    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?

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • robert.diley

    SSC Eights!

    Points: 860

    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....

  • Revenant

    SSC-Forever

    Points: 42467

    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!

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply