16 % -index selectivity -good or bad ?

Question

16 % -index selectivity -good or bad ?

chaithu559

SSCommitted

Points: 1574
More actions
June 11, 2012 at 11:19 pm

#262269

Hi Experts,
I have a non clustered index defined on a column with 4 included columns as well. My query is doing a key lookup , but i drop and look at the execution plan, key-lookup is gone. the selectivity of this index is 16 % , is it good to have this index or drop this index. ALso this index is used by many other queries in other stored procedures.
Please help me in resolving this issue.
thanks
Chaithanya M

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

Without the execution plans and a bit of understanding of your data, it is hard to tell. However, based on the statistics in your database, SQL Server thinks it can execute your query most efficiently by using the index and key lookup. If the statistics is out of date, this may be a wrong guess.

It may for instance be that your index can be scanned or seeked with a predicate using your included columns, and thus returning just a few records for which SQL Server must perform a key lookup. This would be rather efficient, and way more efficient than a clustered index scan. Or, it may be that it actually has to perform a key lookup for 16% of your data, which most likely would be inefficient. Normally SQL Server would not choose such an execution plan, but it can in some cases when there are cached execution plans and a great variety in the number of records for each index key.

Finally, since your index is causing key lookup, you could of course consider widening your index with more columns to make it covering, but I know that is not always an option.

I hope this gave you a bit of insight.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

chaithu559 SSCommitted Points: 1574 More actions · Answer 2

Thank You very much for your valuable reply. One more thing i wanted to know is , whether creating index on a column whose selectivity is 16%

is good or bad ?

Thanks

Chaithanya M

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As Ole said, that's dependent on the data, the queries, the execution plans chosen, etc. It's not hard and fast good or bad.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 4

is the query slow? you say you drop the index and key lookup goes, what is it replaced by, a table or clustered index scan? Is the query faster or slower without the index? Does updating the stats affect the choice of the optimiser?

if the counts for user_seeks and user_scans in sys.dm_db_index_usage_stats are high compared to writes for this index that suggests this is an index SQL finds useful in fulfilling queries.

---------------------------------------------------------------------