SQL Server : Execution Plans and Indexes

Scan vs. Seek

A scan involves looking at all the data in a table, what in Oracle we would call a Full Table Scan. A seek is where you use an index to access the data.

The usual rules apply. If you are expecting to visit most of the rows in a table, a scan is more efficient than a seek. If you are expecting to interact with a small number of rows a seek is more efficient than a scan.

Table Indexes

You can check if there are any indexes on a table using the following command.

USE MyDatabase
GO

sp_helpindex 'MyTable'

If there aren’t any indexes, you are not going to be able to use them.

Displaying Execution Plans

The toolbar associated with a query tab contains the following buttons.

These buttons do the following.

  • Display Estimated Execution Plan : As the name suggests, this button displays the estimated execution plan for the current statement. The estimate will be based on the current statistics, so if the statistics are bad, the estimate may not match the actual execution plan.
  • Query Options : Allows you to alter the way the query is processed. There are lots of different options available, so a discussion of them is beyond the scope of this article.
  • Intellisence Enabled : This is not a performance feature. It’s just used to control whether code completion and tooltips are shown within the query window.
  • Include Actual Execution Plan : When highlighted, running a query will result in the display of an “Execution Plan” tab containing the actual execution plan used by the query. The text at the top of this tab may also include missing index report.
  • Include Client Statistics : When highlighted, running a query will produce an additional tab displaying the client statistics associated with the query.

These suggestions in the missing index report do not include any reference to existing indexes, so it may be better to and an extra column to an existing index, or alter column order, rather than creating a new index. Regardless, this can provide helpful suggestions! To use the missing index report, simply right-click on it and select the “Missing Index Details” menu option. This will produce a commented out script to create the index.

Bookmark Lookups (RID or Key)

If all the data required by the query is not present in the index, you will use the index seek to find the relevant rows in the table, then lookup each row to get the remaining data. This type of lookup is called a bookmark lookup, which can be based on a “RID Lookup” (rowid lookup) or a “Key Lookup”.

You may be able to reduce the number of bookmark lookups by adding columns into the index (a covering index), or including their data into the leaf block if they don’t form part of the lookup key. The following example created a non-clustered index on the MyTable.Name column, but include the Description and CreatedDate values in the leaf block, so you don’t need a bookmark lookup to retrieve them.

CREATE NONCLUSTERED INDEX IX_MyTable_Name  
ON dbo.MyTable(Name)  
INCLUDE (Description, CreatedDate);  
GO

You need to be careful about including too many column into the index as this will increase the overhead of index maintenance, as well as greatly increasing its size.

SET STATISTICS IO

You may find it useful to issue the following statement before issuing a query.

SET STATISTICS IO ON

This will display statistics in the “Messages” tab.

For more information see: