As some of you might already know, Grant Fritchey is working hard on updating his book on execution plans. The refreshed and much-improved third edition should release “real soon now”. And I happen to be involved in this project, as technical editor.

While working on that book, I often ran into interesting and intriguing details in the execution plans. Details that, more often than not, are well beyond the scope of the book. So they will not be included, and rightly so.

But it felt like a shame to have that information not available anywhere. Or scattered across the internet on various sites. So one day, while looking at yet another very interesting operator interaction in yet another execution plan, I decided that I would start to assemble all the information I can find on execution plans, and bring it all together in one single location: The Ultimate SQL Server Execution Plan Reference – or EPR for short.

The plan

I didn’t choose the term “Execution Plan Reference” by accident. The core of the EPR will be a full description of all that is known about every operator known to exist in execution plans: what it does, how it functions, what properties it can have, how those properties affect its behavior, and any additional information that may be relevant to understand the operator. This section will be one page for each operator. Of course, some operators are fairly simple while others are more complex, so those pages will vary in length.

Apart from that core content, I planned some generic pages. It makes no sense to repeat the explanation for properties such as Estimated Number of Rows or Number of Executions on each operator’s page, so instead I wanted to have a single page to list and describe all common properties. I also wanted an introduction page that explains the basics of reading an execution plan, lists the properties for plans as a whole, and debunks some common misconceptions.

And there will be articles with additional background. Instead of having to explain what exactly an “anti semi join” is on each of the four pages for the four join operators, I decided to create a single page describing all the logical join types. When working on Hash Match, the page, was already very long and complex before I even had a chance to start on the details of the “dynamic destaging” process that handles memory spills, so I decided to leave that for a future page. As I continue to work on the EPR, I will probably continue to create or plan separate pages for content that applies to multiple operators, or that I consider too deep and too advanced for the operator’s normal page.

I realize that this is a major undertaking that will take a long time to finish – and given the rate at which new versions of SQL Server are currently released, probably never finish at all. The plan was to make a start and go live once I had “sufficient content”. With no exact idea of how to define “sufficient”.

Progress?

After a quick start during a two-week period of little other work, real life reared its ugly face and progress changed from a fair pace to a slow crawl. I had already hinted at my plans a few time, but didn’t feel ready for go live yet – and given the slow progress, I didn’t dare to set a date either. Until Riley Major picked a T-SQL Tuesday topic that triggered me.

Upon reading that post, I decided that my vague “I’ll go live when it’s ready” and “I’ll work on it when I have time” would not cut it. I needed to set a deadline. So I set myself a deadline: the EPR will go live before June 2018. Whatever content I have completed at that time will be out in the open, and I will post an announcement on my blog.

And to make sure I would not get second thoughts and weasel my way out of this, I committed to this timeframe in public. No way back.

Status

After making that commitment I got swamped with other work even more than before so I now have even less content than planned. I just barely managed to finish the Hash Match page in time (which, admittedly, was a cubic buttload of work), and had to spend a few hours today for a final cleanup sweep across the pages I already finished.

Bit I did manage to have complete descriptions of a few of the more complex operators: three of the four join operators (Nested Loops, Merge Join, Hash Match), both aggregation operators (Stream Aggregate and the aforementioned Hash Match), and a few more generic pages are now live for everyone to see. And with Hash Match out of the way, I really hope to add new pages at a faster pace.

And now …. it is time. I did decide to remove the word “Ultimate” from the title; I might add it back later but for now it is not justified. So now is the time to proudly present … (drumroll)

The SQL Server Execution Plan Reference

The link above takes you to the main page of the EPR. The “Reading guide” paragraph then provides links to the three most important other locations: the generic information (which I recommend as a must-read for everyone; even if you already know a lot about execution plans there might still be some surprises there), the list of operators (which has further links for all operators I already described), and the page that describes properties that are the same for all (or most) operators and that are therefore not included in the property lists of the individual operators.

I hope you like it. If you do, tell your friends and coworkers. If you don’t, tell me why. I might be able to fix it!

“Can I help?”

There are of course a lot of people in the community who know a lot about execution plans. Some of you may know things I don’t. If this applies to you, then yes: you can help. Let me know what you know. Tell me if you see anything that is incorrect or incomplete. With your help, I can make this site better.

But you do not have to be an expert to help me. Say you are looking at an execution plan in one window and the EPR in another window. And the execution plan shows something that the EPR fails to describe. I want to know that too. I do not only want to hear from people that recognize my errors for what they are, I also want to hear from people who are struggling to find an answer and don’t find it on the EPR. Perhaps I am able to figure out the missing information, and I’ll add it. Perhaps I have no clue either but I can then at least document that the issue exists. That might at least safe some other poor souls some time when they run into the same issue, somewhere in the future. Or it might trigger another reader, stir a memory, and then they can tell me how they fixed it.

Or perhaps you don’t see errors, you don’t have unanswered questions, but you simply would like to give some input to help me set priorities. Or you want to point out a spelling error, request a style change, or … well, anything basically!

Whatever your feedback is: you can help me make this site better by sharing it. There is a link to the contact form on every page. I might not always respond immediately. I might sometimes not even respond at all. But I do read every mail I get.

Still here?

Why are you still here? What are you doing reading this final paragraph. Go ahead, click that link and start reading … the SQL Server Execution Plan Reference.