January 19, 2012 at 11:18 am
how does sql engine decide what indexes to use when seeking indexes from table or tables that has clustered and multiple non-clustered indexes. I verified some different scenarios including where clause but can't figure out the pattern.
January 19, 2012 at 11:21 am
"diLip" (1/19/2012)
how does sql engine decide what indexes to use when seeking indexes from table or tables that has clustered and multiple non-clustered indexes. I verified some different scenarios including where clause but can't figure out the pattern.
Engine checks query predicate with available indexes, also checks performance statistics on table as a whole, predicate and indexes columns to know about data distribution then starts trying execution plans until finding one that looks reasonable good.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 19, 2012 at 11:41 am
It's a complex subject and topic. basically the optimizer calculates a series of costs, using different execution plans. It does not consider them all, but considers a number, picks the best one, and goes with it.
January 19, 2012 at 12:05 pm
The precise formulas are totally propriatary, but basically it works off the statistics available about the indexes. The more accurate those stats are to the underlying data and the more applicable they are to a given query, the more likely that index will be used. The size of the index, whether it's unique or not, and, most importantly, the selectivity of the index (how unique is the data within it) are all taken into account.
"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
January 19, 2012 at 1:06 pm
Grant Fritchey (1/19/2012)
The size of the index, whether it's unique or not, and, most importantly, the selectivity of the index (how unique is the data within it) are all taken into account.
And whether it's covering or not.
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
January 20, 2012 at 5:07 am
"diLip" (1/19/2012)
how does sql engine decide what indexes to use when seeking indexes from table or tables that has clustered and multiple non-clustered indexes. I verified some different scenarios including where clause but can't figure out the pattern.
As well as all of the above answers, whether an index is used or not also depends upon t he number of records being retrieved. A table holding only a 100 hundred records will probably be accessed with a top to bottom table scan.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply