SQL Server query plan explanation

This article describes the main operations displayed in the query execution plans of the MS SQL Server DBMS.

Index Seek

Nonclustered index seek. In most cases, this is good for performance, as it is direct SQL Server access to the required rows of data. However, this does not mean that it is always fast, for example, if it returns a allmost number of rows, then its performance will be almost equal to Index Scan.

Index Scan

Nonclustered index scan. This operation is usually bad for performance because it involves sequentially reading the index to retrieve a large number of rows, resulting in slower processing. But there are exceptions, such as using the TOP clause to limit the number of records returned; if you only return a few rows, the scan will be fast enough that you won’t get better performance than you already have, even if you try to rebuild the query/indexes to achieve the Index Seek operation.

RID Lookup

The record ID lookup is a performance bottleneck for the query. But it’s easy to fix: if you see this statement, it means you’re missing a clustered index on the table. At least, you should add a clustered index to get some performance gains for most of your queries.

Key Lookup

Key lookup. Occurs when SQL Server assumes that it can use a nonclustered index more efficiently, and then turn to the clustered index to find the remaining row values that are missing from the nonclustered index. This isn’t always a bad thing: SQL Server falling back to the clustered index to retrieve missing values is quite efficient compared to having to create and maintain additional new indexes.

However, if SQL Server only needs from a Key Lookup operation is a single column of data, it is much easier to add that column to your existing nonclustered index. The index size will increase by one column, but SQL Server will avoid having to access two indexes to retrieve all the data it needs, and it will be a more efficient solution overall.

Sort

Sorting is one of the most expensive operations that can be performed, so it is best to avoid it as much as possible.

A simple way to avoid the sort operator is to have the data stored in a pre-ordered manner. This can be accomplished by creating an index with the key columns listed in the same order that the sort operator uses.

If SQL Server must sort the same data in the same order multiple times in an execution plan, another option is to split the query into multiple stages, using temporary indexed tables to store the data between stages. In this case, if you reuse the temporary table in your query execution plan, you will see a net savings.

Spool

Spools come in different types, but most can be formulated as statements that store an intermediate table of results in the tempdb system database.

SQL Server often uses a spool to process complex queries, dumping data into a temporary table in tempdb for use in subsequent operations. The side effect of this is that the data must be written to disk.

To speed up the query, you can try to find a way to rewrite it in a way that avoids the spool. If that doesn’t work, use the divide-and-conquer method for temporary tables, which can also replace the spool, giving you more control over how SQL Server writes and indexes data to tempdb.

Merge Join

Merge join rarely seen in real queries, and generally the most efficient of the logical join operators.

The optimizer chooses to use a merge join when the input data is already sorted or SQL Server can sort the data at relatively little cost.

The operation is not applicable if the input data is not sorted.

Nested Loops Join

Nested Loops Join is very common. Performs fairly efficient joins on relatively small data sets.

Nested Loops Join does not require the input data to be sorted. However, performance can be improved by sorting the input source; SQL Server can choose a more efficient operator if both inputs are sorted.

The operation is not applicable if the data is too large to be stored in memory.

Hash Match Join

The operation is used whenever other join types cannot be used. It is chosen by the query optimizer for one of two reasons:

1. The data sets being joined are so large that they can only be processed using Hash Match Join.
2. The data sets are not ordered by the join columns, and SQL Server thinks that computing the hashes and looping through them will be faster than sorting the data.

In the first scenario, it is difficult to optimize the query unless you can find a way to join smaller amounts of data.

In the second scenario, if there is some way to get the data in sorted order before the join, such as a predefined sort order in an index, then it is possible that SQL Server will choose a faster join algorithm instead.

Hash Match Joins are quite efficient when they do not flush data to tempdb.

Parallelism

Parallelism operators are generally considered a good thing: SQL Server breaks up your data into multiple pieces to be processed asynchronously across multiple processors, reducing the overall time it takes to complete your query.

However, parallelism can be a bad thing when it is used for most queries. With parallelism, the processors are still doing the same amount of work as without it, taking resources away from other queries that might be running, plus there is an additional averload on SQL Server to break up and then merge all the data from multiple threads of execution.

When parallelism is a performance bottleneck, you may want to consider changing the cost threshold for parallelism tuning if it is set too low.

Stream Aggregate

Stream aggregate groups the rows returned by a query into one or more columns and evaluates one or more aggregate expressions (ex. COUNT, MIN, MAX, SUM, and AVG). The output of this operator can be used by subsequent operators in the query, returned to the client, or both. The Stream Aggregate operator requires input data that is sorted by the columns being grouped. The optimizer uses a Sort operator before this operator unless the data has previously been sorted by a Sort operator or an ordered seek or scan of the index is used.

Compute Scalar

The Compute Scalar operator evaluates an expression and returns a calculated scalar value. This value can then be returned to the user or referenced in an operator, or both can be performed simultaneously. Examples of using these features simultaneously are filter or join predicates. Always returns a single row. Often used to convert the result of a Stream Aggregate to the expected int output type (when Stream Aggregate returns bigint in the case of COUNT, AVG with int column types).

Concatenation

The operator scans multiple inputs, returning each row scanned. Used in queries with UNION ALL. Copies rows from the first input stream to the output stream, and repeats this operation for each additional input stream.

Filter

The operator scans the input data and returns only those rows that satisfy the filter condition (predicate).