# INDEX SELECTIVITY

Discussion in 'Performance Tuning for DBAs' started by sqlnewdba, Sep 6, 2006.

1. ### sqlnewdbaNew Member

I am confused on "Index Selectivity". Two scenarios are below. Being new to SQL Server, I am trying to gain additional knowledge on "Index Selectivity". Please help.

1)dbcc show_statistics (table name, index name)

Updated Rows Rows Sampled Steps

Density Average row length
Sep 5 2006 10:19PM 94755577106451 200 6.4302716E-5 36.0
36.0

What is meant by - 6.4302716e-5? Not a Math major.
What does it compute to?

Using "dbcc show-statistics", is the index a highly
selected index?

2) Copied and pasted script from article on your web site "Tips on
Optimizing SQL Server Non-Clustered Indexes" that uses the Northwind
database. and substituted for my use with same table and index above..

total unique rows - 6209

total rows - 9.47556e+006

selectivity ratio - 0

Confused. The column is not 95% unique. Since it is not, the Query
Optimizer might not even use it. However, it will return less than 5% of
the rows. Reading the article "Not All SQL Server Indexes Are Created
Equal", talks about the 5% rule. If it is less than 5%, the Query Optimizer

Most grateful,

sqlnewdba

E is short for 'times ten to the power of', so 1E-6 is 0.000001 and 1E6 is 1,000,000.
3. ### sqlnewdbaNew Member

Do you know if either scenario qualifies the index as a "highly selective index"?

Thanx
4. ### satyaModerator

The most effective indexes are the indexes with a small percentage of duplicated values. An index with a high percentage of unique values is a selective index. Obviously, a unique index is highly selective since there are no duplicate entries. The database uses these statistics when generating an execution plan for a query.
quote:

The selectivity ratio (Sr) of a potential key value in a table is a good guideline to its usefulness as an index. If the number of rows, which are uniquely identified by the key, is Rk, and the total number of rows on the table is Rt, then

Sr = 100*(Rk/Rt)

The lower the selectivity ratio, the more useful the key would be as an index to the table. SQL Server stores this ratio for each index. Note that if the selectivity ratio of a key is more than 15%, the optimizer will ignore the index and chose a full table scan.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
5. ### sqlnewdbaNew Member

Thank you satya for responding. I did find the formula while "googling" the day before and substituted the values. Since (100*(6209/9475557) = 0, (unless my math is wrong),the index would be used because of the low,(extreme low), selectivity ratio. I gather this formula is a "good rule of thumb".

Could you please shed some light on "density" in the "dbcc show_statistics" with my results posted earlier? Still searching ("googling") for some light (knowledge) on this area.

Thank you
6. ### gurucbNew Member

density = 1/selectivity
7. ### satyaModerator

Density measures how selective an index is. The more selective an index is, the more useful it is, and because higher selectivity means that a query can eliminate more rows from consideration.
quote:The statblob (in sysindexes table) field contains two types of density information: density values and all_density values. The optimizer uses the density and all_density values to determine whether it is better to conduct a table scan or to use an index to access the data.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Satya,

I'm not sure where this quote comes from,

The term "uniquely" seems to be problematic. The sentence is just as true and more accurate without that word there. The inclusion of the word unique then causes people to believe this.

Clearly the notion that 95% of the values need to be unique is false. It's also clear that this misconception is created by using "unique" in your definitions.
9. ### FrankKalisModerator

Welcome to the forums!
Thanks for your contribution, but I guess you have noticed that this thread is 6 years old?