Execution plan operations – scans and seeks

Another post in my ongoing series on reading execution plans. I know I’m jumping around a bit. I hope it makes some kind of sense.

I thought I’d quickly go over the seek and scan operations that can be seen in execution plans. There are 6 main ones. There’s a fair bit that I’m glossing over in this. I’ll get into some details at a later date.

Scans

• Table scan. This operation only appears for a heap (table without a clustered index). The first page in the heap is located based on info in the system tables, and then the pages are read one by one, using the next and, if necessary, previous pointers in the page headers. This is generally an expensive operation and should be avoided where ever possible
• Clustered index scan. Essentially the same operation as for a table scan, just on a table that has clustered index. This operation reads the leaf pages of the clustered index, using the next and previous page pointers. Like with the table scan, this can be an expensive operation and should, wherever possible be avoided
• Index scan. Reading all the leaf pages of a non-clustered index using the next and previous page pointers to navigate. Because non-clustered indexes generally have fewer pages in the leaf than a clustered index, this operation is usually cheaper than a clustered index scan

Note that none of the scans use the index’s b-tree structure to locate data. It’s a straight read of all of the leaf pages.

Seeks

• Clustered index seek. This operation uses the clustered index’s b-tree structure. The seek starts at the root of the tree and navigates down the levels of the index until it reached the leaf page(s) with the desired data. This operation also appears when a partial scan of the table is done, when the index’s tree is used to locate a page, and the index is scanned from that point until another point in the table (possibly the end).
• Non-clustered index seek. Much the same as the clustered index seek, just using a non-clustered index.
• Key lookup. This appeared as a bookmark lookup in SQL 2000, a clustered index seek in SQL 2005 RTM and SP1 and as a key lookup in SQL 2005 SP2. This operation occurs when a seek is done on a non-clustered index to locate one or more rows, but the non-clustered index does not contain all the columns necessary for the query. The clustered index key (which is always included in all non-clustered indexes) is then used to locate the row in the clustered index, to retrieve the remaining data.

Next up, join operations.