The Lazy Read™: How to Make Sense of SQL Server Execution Plans

And Use That Information To Make Your Queries Run Fast!

Photo by Sander Sammy on Unsplash

Each day, thousands of engineers open SQL Server execution plans to tune their queries. Alas, they fail every time! The cycle goes like this: click View Execution Plan, get overwhelmed, close the display, blindly add an index (shoutout to my past self), and then claim moral victory.

If you’ve been there, keep reading, because your victory shouldn’t be moral; it should be practical: A blazing-fast query after actioning on the critical information in an execution plan. Here’s what you need to know to make that happen.

In SQL Server, graphical execution plans come in two flavors:

• Actual Execution Plans — The actual execution plan SQL Server used to execute a query. As the name suggests, it requires the query to be executed.
• Estimated Execution Plans — This is the plan SQL Server anticipates using. It doesn’t require the query to run, just to be parsed and compiled.

Generally, the estimated execution plan is sufficient to begin troubleshooting a slow query and typically closely matches the actual execution plan. Start there, and use the information in this section as a guide for identifying optimizations.

When you open an execution plan in SQL Server, you’ll see a tree of icons. These are physical operators, the steps SQL Server runs to process your query. Execution plans flow right to left and top to bottom.

The icon on the far left is usually the SELECT operator, the final step where results are returned to you. The more complex the query, the more branches and layers the execution tree will have. You’ll often see multiple subtrees with parent, child, and sibling branches for complex queries.

Lastly, each icon displays a cost percentage. In loose terms, this shows how expensive that operator is compared to others in the same part of the plan. You don’t need to know the exact formula SQL Server uses to calculate this number; just know that bigger numbers mean more expensive operations.

Reading Execution Plans: A Heuristic Approach

It’s not realistic or efficient to parse through all of the information in an execution plan. Taking a heuristic approach offers a shortcut for making the information useful. I call this heuristic The Lazy Read™.

The Lazy Read has two components:

1. Identify Critical Operators: Read left to right, focusing on logical operators tied to table access and joins. These operators mark the main junctures of the query’s data flow and are often the most costly.
2. Skim For Potential Red Flags: Certain operators can signal performance issues. These aren’t always problems, but they’re worth a second look.

Also, remember that the cost percentage for each operator is the best guide for locating expensive operations. Those values add critically important context — pay attention to them.

Identify Critical Operators: Access Operators and Joins

Table Access Operators

Here’s a list of table access operators and their descriptions. Table access, along with joins, can easily be the most costly steps in a procedure.

• Index Seek — Nonclustered index lookup where the filter condition targets specific rows.
• Index Scan — A full scan using a nonclustered index.
• Clustered Index Seek — Clustered index lookup where the filter condition targets specific rows.
• Clustered Index Scan — A full scan using the clustered index.
• RID Lookup — Happens when columns not included in a nonclustered index are needed from a table without a clustered index (more on this later)
• Key Lookup — Happens when columns not included in a nonclustered index are needed from a table with a clustered index (more on this later)
• Table Scan — A full scan on a table with no clustered or nonclustered index.

Joins

There are three physical join operators in SQL Server; the procedures of each are described below:

Nested Loops

The query optimizer typically chooses nested loops when joining a small table with a large one. The small table is processed row by row, and for each row, a second, nested operation searches the large table for a match. This inner operation is usually an index seek, not a full scan, but the two-step looping process is what gives the operator its name.

Merge Join

The query optimizer typically selects a merge join when both tables are large and sorted on the join columns. Ideally, each table has a clustered index to support this, but the optimizer can also insert a sort step if needed. Since both inputs are ordered, the join walks through them simultaneously, comparing rows as it goes. Matching rows are returned without requiring nested loops.

Hash Join

The query optimizer typically selects a hash join when joining large, unsorted inputs. It builds a hash table from the smaller input using the join key, then probes this structure using rows from the larger input to find matches. Hash joins are especially effective in complex queries where intermediate result sets are involved. These result sets often aren’t persisted to disk with an index, and this makes it hard for the query optimizer to gauge cardinality estimates. In such cases, the optimizer may perform a nested loop or merge join when a hash join would be optimal.

Now, here are the simplified descriptions that can help you decide when to consider each join type:

• Nested loop: Use when the outer table is small and the inner table is large and indexed.
• Merge: Use when both tables are already ordered by their join keys.
• Hash: Use when both tables are large but not ordered, and your query is complex.

Skim For Potential Red Flags

Here are a few potential red flags to look for in an execution plan. Though these are not always an indication of performance issues that need to be resolved, they are worth a second look.

Scans Vs. Seeks:

Quite simply, the most impactful step you can take to speed up a slow query is adding a missing index that facilitates a critical operation (join, filter, aggregate, etc.). Table scan operators in execution plans are a clear indicator that a query would benefit from adding an appropriate index.

RID and Key Lookups:

RID and Key Lookups happen when you use a non-clustered index to locate rows in a table, but SQL Server must make a second trip back to the table to retrieve additional columns not included in that index.

• A RID Lookup is used for this process when the table doesn’t include a clustered index.
• A Key Lookup is used for this process when the table includes a clustered index.

If either lookup is costly, the easiest adjustment is to add the additional columns to the non-clustered index or, if it makes sense, convert it to a clustered index.

Estimated vs. Actual Row Counts:

When you hover over an operator in an actual execution plan, you’ll see properties including Estimated Number of Rows and Actual Number of Rows. These represent how many rows the optimizer expected versus how many were actually processed.

The optimizer relies on statistics to make these estimates. If there’s a large difference between the two values, the most direct resolution is to update table statistics. This won’t fix every issue, but it’s the most common cause of mismatched row counts.

Inaccurate statistics can lead the optimizer to choose inefficient plans.

Table and Index Spools:

Spools happen when SQL Server stores intermediate results in temp storage, so it can reuse them without re-running the child operators that produced them.

Table and index spools aren’t inherently bad. They often drastically improve query performance. But they might be a costly symptom of inefficient correlated subqueries or missing indexes.

If you see an expensive table or index spool in an execution plan, try these steps:

• Check for correlated subqueries. These often prompt the SQL optimizer to execute an expensive table spool for no good reason. Many times, you can refactor the subquery out of your code — and along with it, the table spool — and still produce the same results. This not only improves the performance of your query but also has the added benefit of being easier to read. Subqueries are often straight hot garbage.
• Check to see if the input tables for the index spool operator are missing the appropriate indexes. An index spool builds a temporary table and adds an index to this table. These indexed rows from this temporary table can then be referenced throughout the query's life. And that highlights the burning question: why not permanently add the index to the input table? Give it a try.

On their own, spools are not bad. In fact, they often drastically improve query performance. A spool is typically a red flag only when the optimizer uses it as a patch for bad design that lives elsewhere in your query or schema. Check for these obvious design flaws before over-analyzing spool properties. That’s the Lazy Read™ way.

A Practical Example

There’s a lot of relevant information here, but how do you apply this knowledge? Here’s a real-world example of how the above information could drastically improve query performance.

• Bad Join Type — In your execution plan, you see a nested loop join, but both tables are large and unsorted. The optimizer is likely using a nested loop here because your query is complex and includes a lot of intermediary results. That means the optimizer may have a hard time estimating cardinality, as table statistics become less reliable when intermediary result sets start to stack. As a test, you force the optimizer to use a Hash join rather than a Nested Loop. And presto! Your query starts running like a well-oiled machine.
• Heinous Correlated Subqueries — As you skim for red flags, you see several table spools in your execution plan, a strong indication that somebody (not you, of course) wrote a few nasty correlated subqueries. You check the code, and lo and behold, somebody got cute and used subqueries to calculate field values. You move that logic from the subqueries into straightforward left joins. Now your query is running lightning fast because the optimizer doesn’t have to offset the cost of those subqueries with a table spool.
• A Wayward RID Lookup — This one is easy; you see a RID Lookup in your execution plan and immediately recognize that you need to add the additional columns to the non-clustered index to prevent the SQL engine from making a second trip back to the base table.

At this point, with three simple adjustments, your query is running orders of magnitude more quickly, your boss is proud, you’re proud, your processing costs are down, and you can clock out early and live to fight another day.

That’s what happens when you lean on heuristics to solve complex problems.