3 deleted 3 characters in body
source | link

What you refer to is the "tipping point" at which the SQL Server optimizer decides to go with a table scan instead of an index seek and key lookup.

There are some things to note about that, the tipping point obviously only affects non-clustered indexes (as the key lookup doesn't have to occur when using a clustered index) and the tipping point also doesn't come into play when your non-clustered index is covering (either all selected columns are in the key columns or in the included columns of the index).

That being said, the number of rows is not 30% of the total rows. It's not a fixed value.

The number of rows is somewhere between 25% and 33% percent of the number of pages, so unless you have 1 row per page the percentage of rows is much much smaller.

See the examples by Kimberly Tripp in The Tipping Point Query Answers

  • If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan.

  • If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan.

  • If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan.

Now to answer your question, how selective should it be before an index is appliedused?

Depending on your row size and number of pages it could be very selective. If you want to make sure your index is used you want your index to be covering.

What you refer to is the "tipping point" at which the SQL Server optimizer decides to go with a table scan instead of an index seek and key lookup.

There are some things to note about that, the tipping point obviously only affects non-clustered indexes (as the key lookup doesn't have to occur when using a clustered index) and the tipping point also doesn't come into play when your non-clustered index is covering (either all selected columns are in the key columns or in the included columns of the index).

That being said, the number of rows is not 30% of the total rows. It's not a fixed value.

The number of rows is somewhere between 25% and 33% percent of the number of pages, so unless you have 1 row per page the percentage of rows is much much smaller.

See the examples by Kimberly Tripp in The Tipping Point Query Answers

  • If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan.

  • If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan.

  • If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan.

Now to answer your question, how selective should it be before an index is applied?

Depending on your row size and number of pages it could be very selective. If you want to make sure your index is used you want your index to be covering.

What you refer to is the "tipping point" at which the SQL Server optimizer decides to go with a table scan instead of an index seek and key lookup.

There are some things to note about that, the tipping point obviously only affects non-clustered indexes (as the key lookup doesn't have to occur when using a clustered index) and the tipping point also doesn't come into play when your non-clustered index is covering (either all selected columns are in the key columns or in the included columns of the index).

That being said, the number of rows is not 30% of the total rows. It's not a fixed value.

The number of rows is somewhere between 25% and 33% percent of the number of pages, so unless you have 1 row per page the percentage of rows is much much smaller.

See the examples by Kimberly Tripp in The Tipping Point Query Answers

  • If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan.

  • If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan.

  • If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan.

Now to answer your question, how selective should it be before an index is used?

Depending on your row size and number of pages it could be very selective. If you want to make sure your index is used you want your index to be covering.

2 deleted 1 character in body
source | link

What you refer to is the "tipping point" at which the SQL Server optimizer decides to go with a table scan instead of an index seek and key lookup.

There are some things to note about that, the tipping point obviously only affects non-clustered indexes (as the key lookup doesn't have to occur when using a clustered index) and the tipping point also doesn't come into play when your non-clustered index is covering (either all selected columns are in the key columns or in the included columns of the index).

That being said, the number of rows is not 30% of the total rows. It's not a fixed value.

The number of rows is somewhere between 25% and 33% percent of the number of pages, so unless you have 1 row per page the percentage of rows is much much smaller.

See the examples by Kimberly TripipTripp in The Tipping Point Query Answers

  • If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan.

  • If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan.

  • If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan.

Now to answer your question, how selective should it be before an index is applied?

Depending on your row size and number of pages it could be very selective. If you want to make sure your index is used you want your index to be covering.

What you refer to is the "tipping point" at which the SQL Server optimizer decides to go with a table scan instead of an index seek and key lookup.

There are some things to note about that, the tipping point obviously only affects non-clustered indexes (as the key lookup doesn't have to occur when using a clustered index) and the tipping point also doesn't come into play when your non-clustered index is covering (either all selected columns are in the key columns or in the included columns of the index).

That being said, the number of rows is not 30% of the total rows. It's not a fixed value.

The number of rows is somewhere between 25% and 33% percent of the number of pages, so unless you have 1 row per page the percentage of rows is much much smaller.

See the examples by Kimberly Tripip in The Tipping Point Query Answers

  • If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan.

  • If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan.

  • If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan.

Now to answer your question, how selective should it be before an index is applied?

Depending on your row size and number of pages it could be very selective. If you want to make sure your index is used you want your index to be covering.

What you refer to is the "tipping point" at which the SQL Server optimizer decides to go with a table scan instead of an index seek and key lookup.

There are some things to note about that, the tipping point obviously only affects non-clustered indexes (as the key lookup doesn't have to occur when using a clustered index) and the tipping point also doesn't come into play when your non-clustered index is covering (either all selected columns are in the key columns or in the included columns of the index).

That being said, the number of rows is not 30% of the total rows. It's not a fixed value.

The number of rows is somewhere between 25% and 33% percent of the number of pages, so unless you have 1 row per page the percentage of rows is much much smaller.

See the examples by Kimberly Tripp in The Tipping Point Query Answers

  • If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan.

  • If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan.

  • If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan.

Now to answer your question, how selective should it be before an index is applied?

Depending on your row size and number of pages it could be very selective. If you want to make sure your index is used you want your index to be covering.

1
source | link

What you refer to is the "tipping point" at which the SQL Server optimizer decides to go with a table scan instead of an index seek and key lookup.

There are some things to note about that, the tipping point obviously only affects non-clustered indexes (as the key lookup doesn't have to occur when using a clustered index) and the tipping point also doesn't come into play when your non-clustered index is covering (either all selected columns are in the key columns or in the included columns of the index).

That being said, the number of rows is not 30% of the total rows. It's not a fixed value.

The number of rows is somewhere between 25% and 33% percent of the number of pages, so unless you have 1 row per page the percentage of rows is much much smaller.

See the examples by Kimberly Tripip in The Tipping Point Query Answers

  • If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan.

  • If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan.

  • If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan.

Now to answer your question, how selective should it be before an index is applied?

Depending on your row size and number of pages it could be very selective. If you want to make sure your index is used you want your index to be covering.