January 7, 2013 at 2:54 pm
ScottPletcher (1/7/2013)
Fewest rows? The number of rows that match all the criteria will be the same regardless of the index used, right?
Match all criteria, yes. What I was thinking about is the case where there are multiple indexes, none of which have all the criteria.
SELECT <columns> FROM SomeTable WHERE Col1 = @a and Col2 = @b-2
and we have a two indexes, one on Col1 and one on Col2. The one that SQL will pick to seek is the one that is estimated to return the fewest rows so that SQL can do the fewest lookups in order to fetch the other column(s), for the secondary filter and the column list if necessary
If all the indexes have the columns for all the criteria, then the index used will be the one with the fewest leaf pages.
What is the basis for you claim that if only 1% of rows in the table match that SQL will almost certainly do a table scan? If the table has 1M rows, but only 10K match, you state SQL is almost certain to do a table scan??
Experience and testing. Yes, SQL is almost certain to scan at 1% of the rows in the table if it does not have a covering index to seek on (Almost certain. I can probably conjure an example where at 1% with accurate row estimations seeks and key lookups are done). Feel free to test it, or to see the many, many blog posts and articles on this subject.
Where did you get that 30% is a hard number for a tipping point?
iirc it's in Kalen's book. Not in the mood for hunting for it tonight.
How could 0.1% of the rows in a table equal 30% of the table pages??
Let's say rows of 100 bytes each. That's 80 per page. 1 000 000 rows is 12 500 pages. 30% of that is 3750 pages. 3750 rows would be 0.375% of the total rows in the table.
In case you misunderstood my comment, tipping point is when the number of rows to be looked up is equal to 30% of the total page count in the table
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 7, 2013 at 3:45 pm
🙂
GilaMonster (1/7/2013)
What is the basis for you claim that if only 1% of rows in the table match that SQL will almost certainly do a table scan? If the table has 1M rows, but only 10K match, you state SQL is almost certain to do a table scan??
Experience and testing. Yes, SQL is almost certain to scan at 1% of the rows in the table if it does not have a covering index to seek on (Almost certain. I can probably conjure an example where at 1% with accurate row estimations seeks and key lookups are done). Feel free to test it, or to see the many, many blog posts and articles on this subject.
Where did you get that 30% is a hard number for a tipping point?
iirc it's in Kalen's book. Not in the mood for hunting for it tonight.
How could 0.1% of the rows in a table equal 30% of the table pages??
Let's say rows of 100 bytes each. That's 80 per page. 1 000 000 rows is 12 500 pages. 30% of that is 3750 pages. 3750 rows would be 0.375% of the total rows in the table.
In case you misunderstood my comment, tipping point is when the number of rows to be looked up is equal to 30% of the total page count in the table
I'm pretty sure the ~30% is the default, but based on what I've read, that is not a hard value because other factors affect it, including memory available, table size, row size, I/O affinity and parallelism.
0.375% is almost 4 times 0.1%; yes, the number goes very low, but I don't think it reaches quite down to 0.1% .
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 7, 2013 at 3:50 pm
ScottPletcher (1/7/2013)
0.375% is almost 4 times 0.1%; yes, the number goes very low, but I don't think it reaches quite down to 0.1% .
I did say "Usually somewhere around 0.1%-0.5%"
p.s. I have seen the tipping point as low as 0.15%
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 8, 2013 at 3:51 am
Thanks to all contributing to this thread, especially Gail and the others who triggered her in providing more information.
This made me wonder about Segments, do they come into the 'picture' at all with the discussed aspects. Does the optimizer 'know' about segments?
Or are segment only a way to 'bundle' pages and therefore help to limit the fragmentation?
Thanks for all the input,
Ben Brugman
January 8, 2013 at 4:07 am
ben.brugman (1/8/2013)
This made me wonder about Segments, do they come into the 'picture' at all with the discussed aspects. Does the optimizer 'know' about segments?Or are segment only a way to 'bundle' pages and therefore help to limit the fragmentation?
Segments?
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 8, 2013 at 7:58 am
GilaMonster (1/8/2013)
Segments?
Sorry I think I mean an extent (8 pages),
how did segment come into my head?
(Old term, Oracle, fantasy, I do not know).
Is the optimizer aware of extents (8 pages of 8 K)?
Ben
January 8, 2013 at 8:16 am
Extents are just 8 contiguous pages aligned on a 64k boundary.
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 9, 2013 at 4:13 pm
GilaMonster (1/7/2013)
ScottPletcher (1/7/2013)
Fewest rows? The number of rows that match all the criteria will be the same regardless of the index used, right?Match all criteria, yes. What I was thinking about is the case where there are multiple indexes, none of which have all the criteria.
SELECT <columns> FROM SomeTable WHERE Col1 = @a and Col2 = @b-2
and we have a two indexes, one on Col1 and one on Col2. The one that SQL will pick to seek is the one that is estimated to return the fewest rows so that SQL can do the fewest lookups in order to fetch the other column(s), for the secondary filter and the column list if necessary
If all the indexes have the columns for all the criteria, then the index used will be the one with the fewest leaf pages.
What is the basis for you claim that if only 1% of rows in the table match that SQL will almost certainly do a table scan? If the table has 1M rows, but only 10K match, you state SQL is almost certain to do a table scan??
Experience and testing. Yes, SQL is almost certain to scan at 1% of the rows in the table if it does not have a covering index to seek on (Almost certain. I can probably conjure an example where at 1% with accurate row estimations seeks and key lookups are done). Feel free to test it, or to see the many, many blog posts and articles on this subject.
Where did you get that 30% is a hard number for a tipping point?
iirc it's in Kalen's book. Not in the mood for hunting for it tonight.
How could 0.1% of the rows in a table equal 30% of the table pages??
Let's say rows of 100 bytes each. That's 80 per page. 1 000 000 rows is 12 500 pages. 30% of that is 3750 pages. 3750 rows would be 0.375% of the total rows in the table.
In case you misunderstood my comment, tipping point is when the number of rows to be looked up is equal to 30% of the total page count in the table
In my experience a "typical" row is longer than 100 bytes, often much longer. So typically 1% of rows will use a nonclus index just fine.
Indeed, because of how often programmers "design" tables, sadly rows are often much longer; in those cases, you can sometimes hit 20%+ w/o tipping to a full scan.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply