Interpreting an execution plan

Question

Hello, I need some help analysing some simple t-sql execution plans. I have a table [User] with the following 3 columns:

Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50))

I have Created an Unique NonClustered Index for the Username column

The execution plan for a query filtering by FirstName:

select * from [User] where FirstName ='John'

shows a Clustered Index Scan with a total cost of 0,0033095

The execution plan for another query filtering by Username:

select * from [User] where Username = 'johndoe'

shows a nested loop containing an Index Seek followed by a Clustered Index Seek with a total cost of 0,00657038! this is twice as much as the cost of the previous query and I don't get it!

There is an index associated with the Username column and no index associated with FirstName, I would expect the second query to be faster.

Answer 1

Total cost is almost always not reliable for things like this

I would use STATISTICS IO and STATISTICS TIME instead

run both queries and look at the difference in reads

SET STATISTICS IO ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe
SET STATISTICS IO OFF

and you can also look at the time by using this

SET STATISTICS TIME ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe'
SET STATISTICS TIME OFF

Answer 2

Exactly as expected... :-)

The first one will be a straight clustered/PK scan

The second will be an index seek on the UserName column followed by a key/bookmark lookup on the clustered index.

You'd normally make the index on UserName into a covering index... which is usually pointless with SELECT * anyway.

Related SO questions:

• Which is better: Bookmark/Key Lookup or Index Scan
• SQL server 2K Bookmark Lookup

Answer 3

From your question I gather that your table is relatively small. As you put more rows in the table you'll find that the bookmark lookup stays about the same, and the scan takes longer and longer. Eventually the scan will cost many times more than the bookmark lookup.

As SQLMenace said, execution plan costs are often unreliable. Use Query Profiler or SET STATISTICS to see what resources are actually being consumed by each query.

Finally, make sure statistics are updated on your table or the engine can make poor choices about which indexes or tables to use in what order.