October 23, 2009 at 6:21 am
Hi All,
What is the major difference between index seek and index scan?
I have read few articles where in index seeks yields better performance.
What exactly happens in case of index seek and index scan??
Any suggestions would be greatly appreciated.
Thanks in advance
October 23, 2009 at 6:40 am
mahesh.vsp (10/23/2009)
Hi All,What is the major difference between index seek and index scan?
I have read few articles where in index seeks yields better performance.
What exactly happens in case of index seek and index scan??
Any suggestions would be greatly appreciated.
Thanks in advance
Bob Hovious just gave a perfect answer to this over at ask.sqlservercentral.com.
I'll recap (probably poorly). A seek is when an index is used to go immediately to a row and then either that row or a small sub-set of rows is returned. A scan is when the table or index is looked at in it's entirety (or most of it, it's not always every single row). So you can see that going straight to one row and only returning it or a few rows is faster than scrolling through all or most rows in a table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2009 at 7:05 am
There's an article on the basics of indexing that will be published on Monday. It has a section on seeks and scans and is written for a novice/beginner. Might be worth a read.
Otherwise
http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/
http://sqlinthewild.co.za/index.php/2009/07/29/is-a-scan-a-bad-thing/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2009 at 3:15 am
Thanks a lot guys.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply