Understanding Graphical Execution Plans - Part 1: Explaining the Plan

  • darrenwhite

    SSChasing Mays

    Points: 634

    Comments posted to this topic are about the item Understanding Graphical Execution Plans - Part 1: Explaining the Plan

  • Grant Fritchey

    SSC Guru

    Points: 396328

    Nicely done. Absolutely excellent first article.

    If I may, I'd like to clarify a couple of points. I wouldn't classify XML as a separate plan type (although I probably did in older editions of my book). The XML plan and the graphical plan are one and the same. It's just how they're displayed.

    For when estimated and actual plans differ, again, nice job, but you might have made the point a little differently. The examples you show are likely to have the plan itself be the same between estimated and actual, but with differences in the details. The main cause of differences between estimated and actual plans, when the plans change, is recompiles.

    On the parallelism, that's a mistake I made in my first book. It's wrong. There are not two plans. There is one. Sorry.

    Again, nice job. Looking forward to the other articles.

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

  • harsh.info

    SSC Enthusiast

    Points: 115

    Nice post.

    Is there a way to determine memory granted to query just before execution and the actual memory it used during runtime. Can we get this information from execution plans.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Well done on the first article. Also, very useful information from Grant.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • rchantler

    SSCrazy

    Points: 2116

    "If an object in the query does not exist, the validation fails and no estimated plan is created"

    Is something executed in this case, i.e. is there an actual execution plan?

  • darrenwhite

    SSChasing Mays

    Points: 634

    Thanks for the clarifications, Grant. After researching available material on execution plans, I can appreciate the amount of work that you had to put in to write your book. It is the only real source there is. The material from Microsoft on the topic is almost non-existent.

  • Grant Fritchey

    SSC Guru

    Points: 396328

    rchantler (2/18/2014)


    "If an object in the query does not exist, the validation fails and no estimated plan is created"

    Is something executed in this case, i.e. is there an actual execution plan?

    No. You don't get a plan. That's an earlier part of the process than plan creation, so nothing is done from there.

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

  • darrenwhite

    SSChasing Mays

    Points: 634

    "If an object in the query does not exist, the validation fails and no estimated plan is created"

    If you try to create an estimated execution plan in this case, you will get an error that the object does not exist and no estimated plan will be created. If you specify to include the actual plan when executing, then the SQL will be executed and the actual plan will be displayed.

  • darrenwhite

    SSChasing Mays

    Points: 634

    "Is there a way to determine memory granted to query just before execution and the actual memory it used during runtime. Can we get this information from execution plans. "

    You can determine the memory to some degree. The actual plan will list the estimated row size, the estimated rows and the actual rows. From this you can calculate an estimated amount of memory used by the data returned.

  • kkp40

    SSC Rookie

    Points: 41

    Nice.

    I would like to read about how to use the execution plans to understand what to change in my sql-query, or in the table, to make it better.

    I can read and understand a graphical execution plan but I don't understand where, what and how to tweak to make it better.

  • Grant Fritchey

    SSC Guru

    Points: 396328

    kkp40 (2/13/2015)


    Nice.

    I would like to read about how to use the execution plans to understand what to change in my sql-query, or in the table, to make it better.

    I can read and understand a graphical execution plan but I don't understand where, what and how to tweak to make it better.

    Reading the plans should lead to an understanding of the choices made by the optimizer. Since those choices are determined by the indexes, statistics, constraints and the T-SQL code binding it all together, it should then lead to determining what to do about it. For lots more detail, see the two books in my links below.

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

  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    Really nice article 🙂

  • This was removed by the editor as SPAM

Viewing 13 posts - 1 through 13 (of 13 total)

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