Understanding Execution Plans

Understanding Execution Plans

To me, it feels as if 2014 is a long time away. But it isn’t. Sinterklaas has already dropped off his presents and is probably already back in his castle in Spain. Christmas is almost upon us. And before we know it, we’ll be washing oliebollen down with Champagne.

That also means that I need to get cracking on preparing my precon seminar on execution plans. What precon seminar you say? Oh right – I didn’t tell you yet. The good folks organizing SQL Saturday #269 in Exeter (UK) (on March 22) have decided to extend the event with a full training day on Friday March 21. For that purpose, they invited eight of the best SQL Server speakers in the world, and me, to spend a whole day pouring our knowledge into other people’s heads.

The full program for this excellent day can be found here – and believe me, if I was not speaking that day, I would be trying to clone myself and visit at least four of them!

My session on that day, as you can see, focuses on execution plans. So what can you expect if you sign up? Here is a short overview.

I will start with the bare basics. What is an execution plan, what is its role in a SQL Server database, and most of all – how can you get to see it, how do you start reading it (Left to right? Right to left?), and how do you get to those extremely interesting details that Microsoft has carefully hidden from view? Why is an estimated plan not estimated, why is an actual plan actually mostly an estimated plan? What is an iterator, what does it do, and how does it interact with other operators?

After that, we’ll dive into the details. What is a seek, what is a scan? How do you notice when a seek secretly scans, and why should you care? Why do people say scans are bad, and why are they wrong? (Or, maybe even more important, WHEN are they wrong, and when not?)

Next up are the joins. Inner, outer, loop, merge, hash – but also semi, and anti semi. Yes, those are all join types, and we will explore them all. How do they work, what are the benefits and what are the costs of all of them? How can you influence the type of join chosen, and why would you want to?

All that, and much more, will be included in my seminar. And that’s just what I have planned for before lunch. At the end of the day, you can expect to have a good working knowledge of almost every iterator that you can encounter in an execution plan. I will also explain what has changed in execution plans in SQL Server 2012 and SQL Server 2014. And to ensure that nobody falls asleep from my constant talking, I will give you all some exercises in between, challenging you to immediately apply what you just learned. These exercises may not be the kind of exercises you expect – but trust me, you will find them to be fun and refreshing, and they will also learn you how to apply your knowledge to all kinds of problems.

Does this sounds interesting or useful at all to you? I hope so – why else are you on a SQL Server blog site? So get cracking – click this link and sign up for my precon right now. (Or for one of the other precons – they are all great!) If you do so soon enough, you can still apply for the special “Early Bird” rate of only £150 – which is valid until December 15. But don’t despair if you see this post later – from December 16 until February 28, you still only pay £185 – still a steal for a whole day of training! (Even the £200 last minute rate that applies from March 1st is great value – but honestly, why wait?)

I am looking forward to visiting Exeter this March. I hope to see all of you there. Either in my precon – or if you choose to attend one of the other precons, then maybe in one of the many sessions that will take place the next day.

SQLRally and SQLRally – Session material
Parameterization and filtered indexes (part 1)

Related Posts

No results found.

5 Comments. Leave new

  • Bruce Dunwiddie
    January 5, 2014 04:14

    I’m working on some classes I’m planning on teaching internally at our company coming up and of course I have to discuss this same topic as part of that series. Your topics are fairly standard, but I think they’re showing the same gaps that seem to have left a lot of developers still not truly understanding execution plans. The first gap is that I don’t think you’ve honestly asked yourself who your audience is. By explaining seeks vs scans, you’re basically tailoring to beginners, but then by going into depth on all the operators, including changes as of 2014, you’re tailoring towards advanced people. The second gap, which I think is the classic gap, is that advanced people don’t just look at an execution plan and go from there. They read the query, set an expectation on what the execution plan should be, then review the execution plan vs their expectations.    

    Reply
  • Bruce Dunwiddie
    January 5, 2014 04:21

    So why do we teach the other way around? I currently believe that the first question that should be asked and then answered in these classes is "what table does a query start in?". I have found very few people, even advanced TSQL developers, who can answer this question correctly. The next question is "then what?". I honestly believe we need to teach from the query to the execution plan, not the execution plan back to the query, because people start doing very odd things to the query to get different execution plans, not understanding how what they were originally asking the database to do in the query drove the execution plan.

    Reply
  • Hugo Kornelis
    January 5, 2014 13:48

    Bruce: Thank you for your frank and constructive feedback!

    I will defintely take your first point to heart. This subject does range from beginner to very experienced, and it is a challenge to overcome that. During preparation and on the day itself, I will try very hard to add enough advanced details to the starting stuff, and to ensure that the advanced stuff can be understood by all. My goal is to explain everything, even the most advanced topics, in a way that everyone on a 300-level can understand. (Even 200-level would be able to understand the explanations, but they probably lack sufficient context to piece the details together and understand the relevance).

    I disagree with your second point – or rather with the conclusion you based on it. It is true that advanced people usually know what they want to see before looking at a plan, but they can only get there from a thorough understanding of the basics. If you don’t know (and understand!) the iterators available to the query engine, how can you ever expect to have an idea of what plan to expect (or to aim for) for a query?

    Incidentally, the exercises I will include in this day are all intended to target this area. My explanations focus on the individual components, but the exercises will encourage the students to think about the optimizer’s decisions: which iterator and/or which combination of iterators is a good choice for what kind of query? (I just re-read my post, and I was surprised to see that I forgot to mention these exercises – shame on me! I will edit my post to remedy this omission)

    Reply
  • Hi Hugo

    It was a great course, as was your presentation to the user group the night before.

    Have you posted the slides/examples from the Thursday?

    Thanks

    Martyn

    Reply
  • Hugo Kornelis
    May 29, 2014 19:10

    Hi Martyn,

    Thanks for the kind words!

    I have asked Jonathan and Annette to distribute a copy of the slide deck and demo code for my precon to all attendees, and I have also asked them to upload a copy of the deck I used on Thursday to the user group site – have you looked there?

    If you cannot find it, then please send me your email and I’ll send you a copy directly.

    Cheers,

    Hugo

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close