Understanding Graphical Execution Plans - Part 1: Explaining the Plan

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

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

  • 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

  • "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?

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

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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

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

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Really nice article 🙂

  • This was removed by the editor as SPAM

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

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