THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Understanding Execution Plans

This blog has moved! You can find this content at the following new location:

Published Thursday, December 12, 2013 11:19 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Bruce Dunwiddie said:

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.    

January 4, 2014 8:14 PM

Bruce Dunwiddie said:

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.

January 4, 2014 8:21 PM

Hugo Kornelis said:

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)

January 5, 2014 5:48 AM

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

There are two ways to test how your queries behave on huge amounts of data. The simple option is to actually

March 2, 2014 5:35 AM

Martyn said:

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?



May 29, 2014 11:52 AM

Hugo Kornelis said:

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.



May 29, 2014 12:10 PM

Python Project Help said:

I am so happy to read this. This is the kind of manual that needs to be given and not the random misinformation that's at the other blogs.

February 24, 2018 2:53 AM

do my online exam said:

Thanks a lot for the post. It has helped me get some nice ideas. I hope I will see some really good result soon.

February 24, 2018 2:53 AM

Do My C Programming Project said:

I Personally Like Your Post, You Have Shared Good Article. It Will Help Me In Great Deal.

May 28, 2018 12:25 AM

Harvard Business Review HBR Cases Solutions and Analysis said:

Those Who Come To Read Your Article Will Find Lots Of Helpful And Informative Tips.

May 28, 2018 2:53 AM

muneer said:

Good composed article. It will be steady to any individual who uses it, including me. Continue doing what you are doing – can'r hold up to peruse more posts.

December 12, 2018 5:07 AM

Jones said:

I discovered such a large number of fascinating stuff in your web journal particularly its discourse.

December 13, 2018 10:11 AM

Elizabeth said:

I have Seen lots of positive comments in this article so i guess its one of the best informative article…

December 13, 2018 8:00 PM

jeweller said:

Heya just wanted to give you a brief heads up and let you know a few of the pictures aren’t loading properly.

December 14, 2018 4:37 AM

muneer said:

A portable fish finder is a good choice if you don't fish from the same ship all the time.

December 18, 2018 6:56 AM

Jones said:

I feel really nice reading these articles I mean there are writers that can write good material.

December 27, 2018 7:50 PM

Malissa said:

I will be sure to bookmark it and return to read more of your useful information. Thanks for the post. I’ll certainly comeback.

December 28, 2018 8:47 AM

Russo said:

i adore perusing this article so beautiful!!great work!

January 5, 2019 7:27 AM

Jones said:

I would like to thank you for the efforts you’ve put in writing this website. I’m hoping the same high-grade web site post from you in the upcoming also.

February 13, 2019 2:22 AM

Russo said:

I have seen wonderful websites and I have caught not so great websites. This site is very informative in many ways and certainloy ranks in the former category.

March 23, 2019 9:09 PM

Jones said:

A debt of gratitude is in order for the pleasant web journal. It was extremely valuable for me. I’m upbeat I discovered this site. Much obliged to you for offering to us,I too dependably discover some new information from your post.

March 28, 2019 10:07 PM

Jones said:

Good composed article. It will be strong to any individual who uses it, including me.

March 30, 2019 8:14 PM

muener said:

Just pure brilliance from you here. I have never expected something less than this from you and you have not disappointed me at all. I suppose you will keep the quality work going on.

April 6, 2019 12:58 AM

Jones said:

Every one of the substance you said in post is too great and can be exceptionally valuable

April 11, 2019 7:12 PM

Laila said:

It is an extraordinary site.. The Design looks great.. Continue working like that!..

April 11, 2019 7:12 PM

Thủy Lực said:

July 3, 2020 2:04 AM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement