SQL Server’s Graphical Execution Plan contains a wealth of information that could be used to diagnose performance and maintenance (such as out of date statistics) issues.

Every SQL query has an execution plan, which are the steps required to perform the required operation, this execution plan is either saved for later usage (generally done by encapsulating the query in an object such as a Stored Procedure), or a plan is recompiled every time a query is run (either accomplished by running queries directly against the database, or with query hints).

Its important to understand every element in the execution plan, in order to accurately diagnose the query, and deduce relevant actions to either improve the query, or better understand how your data is behaving.

Lets take a look at the “Look-up” operations found in the SQL Server Graphical Execution Plan

Table Scan

Table Scan means that the rows were returned by scanning the entire table in question. One would generally get that when they do a straight SELECT off a table with no SARGE (or filtering) expression and no relevant index on the table. Basically means that the optimiser has to go through every row in the table to return the required data set.

To Optimise Table Scan:

• Add a WHERE clause to make the query SARGE-able
• Adding a relevant index that corresponds to the WHERE clause

Clustered Index Scan

Pretty much similar to a Table Scan, were all (or large percentage of) rows need to be examined in order to return the required data set, although with the exception of a relevant clustered index on the table.

To Optimise Clustered Index Scan:

• Add a WHERE clause to make the query SARGE-able

Clustered Index Seek

This means that the SQL optimizer has used the Clustered Index to get the data through utilising the index key values. Sometimes the SQL optimiser has to sort the data-set before outputting it, you can see this by hovering over the icon in the execution plan and looking up the value of the “Ordered” property.

To Optimise Clustered Index Seek:

• Ensure statistics are up to date and part of the maintenance plan

Non-Clustered Index Seek

Similar to a Clustered Index Seek, although it is important to note that since a Clustered Index contains the actual rows of the table, where as Non-Clustered Index contains only the index key values, and then references to the actual data, which will either be a reference to the Clustered Index, or to the Heap.

This means that if the Non-Clustered Index is Non-Covering (in relation to the query being executed), the optimiser will have to get the remaining data from the table itself. This will appear in the execution plan as a join with the Clustered Index or the table itself.

To Optimise Non-Clustered Index Seek:

• Ensure that the Non-Clustered Index is a Covering Index

Key Lookup

A Bookmark Lookup operation, Generally pops-up whenever you have a non-covering index, that has to use another index to full fill the query. For example if a Non-Clustered Index is used to satisfy a query that requires an output column (in the SELECT list of the query), which is not part of the Non-Clustered Index column list.

Key Lookup is generally accompanied by a Join type (see Part 2 of the SQL Server Graphical Execution Plan Icons Walkthrough series).

Key Lookup appears if a Clustered Index exists and was used to satisfy the query. If non exists, then generally a RID Lookup is used instead

To Optimise Key Lookup:

• Ensure that the Index used is a Covering Index

RID Lookup

A Bookmark Lookup operation similar to Key Lookup, although instead of using a Clustered Index, the bookmark operation occurs on a heap table.

This happens if there is no Clustered Index on the table.

To Optimise RID Lookup:

• Ensure that the Index used is a Covering Index
• Creating a Clustered Index on the table will change this operation to Key Lookup, which might be faster in some cases.

Stay tuned for the next two posts in this series, Join Operations and Aggregate Operations.