Comparing Execution Plans

  • Grant Fritchey

    SSC Guru

    Points: 395417

    Comments posted to this topic are about the item Comparing Execution Plans

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

  • This was removed by the editor as SPAM

  • Grant Fritchey

    SSC Guru

    Points: 395417

    Stewart "Arturius" Campbell (12/13/2016)


    That is a really neat addition to SSMS.

    However, still to be taken with a pinch of salt.

    How do you mean? It's just comparing the underlying XML on the plans. The differences are different. It's not saying that one plan is better than the other, simply that the values found between the plans are different. In the example given, the plans are the same, but the time to unpack the query using the derived tables was longer than the one using regular tables.

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

  • Toreador

    SSChampion

    Points: 11225

    Where in the question did it state that this was SQL2016?

    Even with the new option, why are the tooltip and properties answers incorrect? They're just presenting different views of the same data aren't they?

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Great question Grant, definitely learned something.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Toreador (12/14/2016)


    Where in the question did it state that this was SQL2016?

    There shouldn't be. Always expect the latest (officially released) version of SQL Server, unless explicitly stated otherwise.

    Toreador (12/14/2016)


    Even with the new option, why are the tooltip and properties answers incorrect? They're just presenting different views of the same data aren't they?

    Yes. But it is obvious one answer is more efficient than the others. There have been lots of questions in the past where you had to pick the most efficient method.

    Furthermore, there were two answers that did the same: checking the tooltip and checking the properties window. Since both are the same, but you can only select one answer, it automatically rules out those two answers.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Heals

    SSCrazy

    Points: 2232

    Koen Verbeeck (12/14/2016)


    Toreador (12/14/2016)


    Where in the question did it state that this was SQL2016?

    There shouldn't be. Always expect the latest (officially released) version of SQL Server, unless explicitly stated otherwise.

    Toreador (12/14/2016)


    Even with the new option, why are the tooltip and properties answers incorrect? They're just presenting different views of the same data aren't they?

    Yes. But it is obvious one answer is more efficient than the others. There have been lots of questions in the past where you had to pick the most efficient method.

    Furthermore, there were two answers that did the same: checking the tooltip and checking the properties window. Since both are the same, but you can only select one answer, it automatically rules out those two answers.

    Was typing out a long winded way of saying the same thing - glad I checked the replies before hitting the button 😛

    On a side note - started using 2016 yesterday at home after only ever working on 2008R2 (Been meaning to do it for a while, along with learning C# - it's happening in the new year so prepping the machine ;-)). New functionality will take some getting used to (How / When to apply it etc.). Most annoying thing I've found thus far in SSMS is the fact that when you hover over a field, it doesn't give you the length. Alt +F1 on the table gets around it to an extent...

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Nowhere in the question is stated that the SSMS is 2016 version. "Compare Showplan" is not available in the lower versions.

    So the answer is not always correct.

    "Open the properties page for each operator and compare them" is most correct.

    Igor Micev,
    My blog: www.igormicev.com

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Igor Micev (12/14/2016)


    Nowhere in the question is stated that the SSMS is 2016 version. "Compare Showplan" is not available in the lower versions.

    So the answer is not always correct.

    As said before, always assume most recent version unless explicitly stated otherwise.

    Otherwise almost any question can be countered with "yeah but it doesn't work on SQL v7".

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ed Wagner

    SSC Guru

    Points: 286957

    Grant, great question that made me learn something. Thanks.

    And I don't think it's a problem that it won't work on SQL 6.5. 😉

  • sknox

    SSChampion

    Points: 12216

    The question is written as "How can you identify any differences?"

    However, 3 of the four answers fit that question as written.

    The question should have been "How can you most easily identify all differences?"

    SQL professionals should know the importance of phrasing a query properly.

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    sknox (12/14/2016)


    The question is written as "How can you identify any differences?"

    However, 3 of the four answers fit that question as written.

    The question should have been "How can you most easily identify all differences?"

    SQL professionals should know the importance of phrasing a query properly.

    True.

    However you can only select one answer.

    In real life situations, if you have 3 alternative solutions to a problem and one is clearly the most efficient, which one are you going to choose?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • paul s-306273

    SSChampion

    Points: 10545

    Great feature.

  • sknox

    SSChampion

    Points: 12216

    Koen Verbeeck (12/14/2016)


    sknox (12/14/2016)


    The question is written as "How can you identify any differences?"

    However, 3 of the four answers fit that question as written.

    The question should have been "How can you most easily identify all differences?"

    SQL professionals should know the importance of phrasing a query properly.

    True.

    However you can only select one answer.

    In real life situations, if you have 3 alternative solutions to a problem and one is clearly the most efficient, which one are you going to choose?

    In real life, if you were doing code review and saw a query of the form:

    SELECT TOP 1 ....

    WITHOUT an ORDER BY clause, with knowledge that the query without TOP would return multiple rows with different values, would you pass it?

  • MMartin1

    One Orange Chip

    Points: 27488

    Koen Verbeeck (12/14/2016)


    Igor Micev (12/14/2016)


    Nowhere in the question is stated that the SSMS is 2016 version. "Compare Showplan" is not available in the lower versions.

    So the answer is not always correct.

    As said before, always assume most recent version unless explicitly stated otherwise.

    Otherwise almost any question can be countered with "yeah but it doesn't work on SQL v7".

    It doesnt take much effort to list the version you are working with in this problem. I can interpret a lack of version information as meaning this is universal to most (if not all).

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

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

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