SQL Server Execution Plan Reference

Welcome to the SQL Server Execution Plan Reference!

This site is for SQL Server developers and DBAs who want to have a full understanding of execution plans. For every operator, you will find a description of what it does, how it does it, and how it interacts with other operators. You will also find all properties and their meaning.

This guide covers all versions of SQL Server since SQL Server 2005, including Azure SQL Database. Any information applies to all these versions, unless marked otherwise.

Reading guide

This site is a reference, not a beginners guide. If you are new to execution plans, I recommend starting on the basics first. An excellent resource for that is Grant Fritchey’s book on Execution Plans.

Here is how to use this guide:

First, start at the Generic Information. This page contains important information about execution plans in general, and dispels some common myths.
Most of the information can be accessed from the complete Operator List, which has links to pages describing the individual operators.
The Common Properties page contains information on properties that all or most operators use. You might want to have that page open at all times.
On Plan properties, you find detailed information about properties on the top-left operator (usually the Result or Language Element operator), that give information about the plan as a whole.
The Change log can be useful if you visited this site before and want to see which pages have been updated since.

Background

There are many ways to improve query performance. For some, such as replacing cursors with declarative code, execution plans are not needed at all. Most require a developer or DBA to at least look at an execution plan. Most then look for specific patterns with a known improvement method (such as introducing a covering index to remove a Key Lookup operator).

If you are really serious about query tuning, you need to move one or more levels deeper. You want to view an execution plan as not just a container for those three of four patterns you memorized, but as an accurate and complete description of exactly how SQL Server executes the query. Once you understand all those steps, in intimate detail, you will understand why the query performs suboptimal. And that, in turn, can help you improve its performance.

Unfortunately, there is no comprehensive in-depth documentation available on execution plans. The information in Books Online on Showplan Operators is extremely shallow. Once you really get into the fine details of execution plans, you often need more information. And then you have to try to find relevant blog posts, or do trial and error until you understand how it works.

That is the gap that this site tries to fill. I want you to find all the information you need here, in one place, so you don’t have to go searching for it.

Disclaimers

This website is a volunteer project. It is not supported in any way by Microsoft. I try to be as complete and as accurate as possible, but I am human. If you find any errors, let me know!

Some of the information on this site describes undocumented behavior. These descriptions are usually based on observations and extrapolation; there is no guarantee that this is 100% accurate. Also, Microsoft can change any undocumented behavior without notice.

Because this site is a work in progress, information given may not always be complete. Pages known to be incomplete are clearly marked as such. However, even pages not marked as incomplete may still miss relevant information.

Legal disclaimer: All information, products, and other content included in or accessible from this website are provided “as is” and without warranties of any kind (express, implied, and statutory, including but not limited to the implied warranties of merchantability and fitness for a particular purpose).

Everything on this website is copyrighted by the author, Hugo Kornelis, unless explicitly stated otherwise. Everyone may freely link to this content. However, without prior permission it is NOT allowed to republish any of the information here, or to include this information in any commercial product.

Feedback

If you have any feedback on this site, please let me know – I love to hear from you!

I especially want to know if you see anything that appears incorrect or incomplete, or if you see an execution plan that contradicts what you read on this site. When you report such issues, please include full reproduction steps (starting with an empty database) if possible. Also include the execution plan (as a .sqlplan file), plus the full text of the query and any views or user-defined functions used in it.