I want to better understand execution plans

Question

I want to better understand execution plans

jeffem

Ten Centuries

Points: 1222
More actions
June 14, 2012 at 9:39 am

#262401

I would appreciate some recommendations for resources that would help my learning execution plans better.
I am an advanced (or so) SQL developer with a decent understanding of indexing (for example, I have read and thoroughly understand the Stairway to Indexes series on here). I want to learn in order to develop better code and back-ends for apps, but I don't want to bog down in the DBA-level information where it's not useful to my area of interest.
I suppose what I mean with that, for example, is that I should learn some information about wait times and types, but I wouldn't expect to need an encyclopedic knowledge of them, as I'm not going to be supporting any systems and the like.
I can currently look at an estimated plan and get an idea (sometimes) of what might need to be improved, but sometimes it's half-guessing, based more on what indexes I see and how the query is structured, rather than the details of the plan itself. I can see multiple plans and usually have an idea of which one is better, but not always an idea of how to get to the better one.
Thoughts?

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

RedGate has a book on that subject: http://www.red-gate.com/community/books/sql-server-execution-plans

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

In addition to my book (thanks for the reference) I write about execution plans a lot on my blog, scarydba.com. For lots of even more detailed information, low level stuff, I suggest going to Paul White's blog: http://sqlblog.com/blogs/paul_white/

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

jeffem Ten Centuries Points: 1222 More actions · Answer 3

Thanks to both of you for the replies!

And for the excellent personal service, I'll get my company to spring for the published version of the book, instead of just the PDF. :satisfied:

Bouke Bruinsma SSCrazy Points: 2122 More actions · Answer 4

For me Fritchey's book was a good start. In practice I use the nr of reads together with exec plans a lot. Before running a query, set statistics io and time on and try to understand from the exec plan why the nr of reads is high (or low) or why the duration or cpu is high. In most cases this will point to improvevement possibilities quickly if any.