July 5, 2009 at 8:10 am
Hello Experts
Could you please let me know how the SQL Query Analyser determines whether it will go for Index Seek or Index Scan?
Can you please provide me some clear distinction between these two?
I get a very little scope to check on this.
Best Regards
Sourav
---------
(Hyderabad, India)
SQL DBA
Thanks.
July 5, 2009 at 9:55 am
Sourav Mukherjee (7/5/2009)
Could you please let me know how the SQL Query Analyser determines whether it will go for Index Seek or Index Scan?Can you please provide me some clear distinction between these two?
Index seek is when your query points to the exact 'match' and then reads the exact row required by your query.
Index scan is scanning the entire index for the 'match' and then reading the row(s).
Obviously, you can guess that index scan is 'bad' if it is happening for large tables as it would scan the entire index each time a related query is run. For smaller tables, Index scan is still acceptable but for larger tables the difference in time is quite high.
This URL http://blogs.msdn.com/craigfr/archive/2006/06/26/647852.aspx will help you in finding the difference between the two.
July 6, 2009 at 8:46 am
The Optimizer uses statistics to determine if a seek or a scan is the likely "best" access method for a given query. It actually takes a very small percentage of rows to for the optimizer to choose a seek. See this blog post by Kimberly Tripp to see how it works.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 6, 2009 at 11:56 am
Jack Corbett (7/6/2009)
The Optimizer uses statistics to determine if a seek or a scan is the likely "best" access method for a given query. It actually takes a very small percentage of rows to for the optimizer to choose a seek. See this blog post by Kimberly Tripp to see how it works.
And like almost all SQL Server questions, even she winds up saying "it depends". I did enjoy that series of blog entries (was on vacation when she posted the questions).
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply