Seek operator presents both physical and logical operator. It can only apply to an index, clustered index or none clustered index. It’s the most efficient operation to reach a record in an index by key. As we know, indexes are organized in a B-Tree, each record in the non-leaf level of B-Tree includes a the first key(s) in the page of next level and the pointer (File:Page:Slot) of the page in the next level.

While seeking, the root page is read, from there, SQL Server figures out the rage of the page in the next level and determin which page(s) in the next level should be retrieved. If the page(s) in the next level is(are) the intermediate level page(s) again, SQL Server checked the range of next level again until it reaches the leaf level pages.

After the first page at leave level retrieved, our predicate may not exactly point to the first record in the page at leave level, so SQL server will have to find the first record in the page. Records in the page is NOT ordered in an index but slot array is (See Paul Randall’s block at sqlskills.com, you should know him or at least his blog:), I may blog is in the future as well). In this case, SQL server may performs a binary search (not sure since it’s not officially announced).

After the first record of the key(s) gets located, SQL Server may not output that record because an index may have an INCLUDEd column, the query might have a search on that column. Then SQL Server will perform a scan in this case to find a predicate on the included column. This will show up in the property of seek operator as Predicate (Seek Predicate in the property is only for the index keys). So you think there some similarities to what we talked?

Yes, this behavior is very similar to an index scan. why? The index scan starts from root page as well, goes through the intermediate page and reaches the first/last record of the index and then perform a scan through doubly linked chain of the page. The index seek does exactly the same operation of an index scan! Interestingly, The cost of index seek is exactly same as index seek!

CPU cost = 0.1581 ms + 0.0011 ms per extra row
IO cost = 3.125 ms + 0.7407 ms per extra page

That’s the index Seek for today, and it’s also a Friday. Hope you have a good weekend and good rest. Howevery, I would like to give you a “puzzle” as part of your weekend enjoyment. As we know that SQL Server does not give you the best plan but optimal, here is the code. Would you tell me which one is more efficient? Looking forward to your replies. I will give you the answer next Monday.

use AdventureWorks2008R2

--set statistics io on -- this is the hint
select CustomerID, SalesOrderID 
from Sales.SalesOrderHeader with (index=[IX_SalesOrderHeader_CustomerID])
where CustomerID = 29974 and SalesOrderID = 45785

select CustomerID, SalesOrderID 
from Sales.SalesOrderHeader 
where CustomerID = 29974 and SalesOrderID = 45785