THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

https://SQLServerFast.com/blog/hugo/2013/12/understanding-execution-plans/

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

Comments

 

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?

Thanks

Martyn

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.

Cheers,

Hugo

May 29, 2014 12:10 PM

Leave a Comment

(required) 
(required) 
Submit

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