Execution Plan Training Suggestions

Question

Execution Plan Training Suggestions

RonMexico

Hall of Fame

Points: 3474
More actions
March 24, 2017 at 7:35 am

#319908

I'm wanting to learn much more about execution plans so I can better tune the queries we run. I've thumbed through some Grant Fritchey books and have messed around with SQL Sentry's Plan Explorer. The material provides me with a basic understanding of how to read the plans, identifying the costly operations, etc but I'm needing more guidance on how to address the problems. The reason for starting this is that I'm really just looking for suggestions on books, videos, courses, real life experiences, software, or anything else that helped you get a good grasp of execution plans so that I can take the next step in what I'm looking to achieve.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

RonMexico - Friday, March 24, 2017 7:35 AM
I'm wanting to learn much more about execution plans so I can better tune the queries we run. I've thumbed through some Grant Fritchey books and have messed around with SQL Sentry's Plan Explorer. The material provides me with a basic understanding of how to read the plans, identifying the costly operations, etc but I'm needing more guidance on how to address the problems. The reason for starting this is that I'm really just looking for suggestions on books, videos, courses, real life experiences, software, or anything else that helped you get a good grasp of execution plans so that I can take the next step in what I'm looking to achieve.

Have you considered taking a course? You'd be surprised just how much you can learn in a couple of days.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RonMexico Hall of Fame Points: 3474 More actions · Answer 2

That's definitely something I'm open to the idea of. Initially I'm looking for some free or very inexpensive resources to get an idea if it's something I can wrap my brain around before I have the company expense some training and risk coming back with nothing to show.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

SSC.com's own Grant Fritchey wrote the book on the topic, and it is FREE:

SQL Server Execution Plans, 2nd Ed.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

RonMexico Hall of Fame Points: 3474 More actions · Answer 4

I do have a copy of that but, admittedly, I've only skimmed through picking out key topics and trying to apply them to my own experiences. I do plan on taking a deeper dive back into that book but I'm still curious if there is anything in conjunction with the book or separate materials people have found really useful while they were learning.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

The problem that you may be having is that you "thumbed through" Grant's books. If you do a deep study of his books, everything you ask for will become self realizing with some practice on your part. I'll also state that the better you are at T-SQL and the better you have an understanding of Indexes and how the optimizer might select which one to use (partially covered in Grant's books), the better you'll be at using Execution Plans to figure out how to improve the performance of code either with the appropriate index or by tweaking'n'peaking the code itself.

Do yourself a favor... go back and do a deep dive on Grant's books. Spend some time generating test data to duplicate some of the things he talks about in the book and understand why they work the way they work. Then you'll be able to understand why things like adding a non-clustered index might break your "perfect" clustered index lookups when the second column of the new non-clustered index is used as a scan instead of doing "perfect" seeks on the clustered index.

If that doesn't suit you, then you can wade through miles of posts on "performance tuning" where folks do post Before'n'After execution plans. IMHO, there is no single source better than Grant's books in achieving the understanding necessary to become good at "performance tuning".

Of course, no matter the method of learning you choose, you'll also need to learn techniques like "Divide'n'Conquer", pre-aggregation, writing SARGable code, and have a full understanding of what things like "Datatype Mismatches" can do to kill performance especially where (but not limited to) ORM code is concerned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonMexico Hall of Fame Points: 3474 More actions · Answer 6

Fair enough. Thanks for all of the advice! I'll hit the books and build from that.