Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 26 '12 at 14:33

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
1  
+1 for updating statistics –  mtpettyp Nov 19 '10 at 18:56

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
share|improve this answer
    
+1 for noting that execution plan cost is not reliable. –  ErikE Sep 10 '10 at 4:21
    
yeap! +1 for the same reason too –  Gnomo Sep 10 '10 at 9:12

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:

share|improve this answer
    
+1 for adding related questions –  Gnomo Sep 10 '10 at 9:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.