1

I've just read this interesting blog post about selectivity notion. In the section 'When is better to not use a database index?', the author states :

Accessing the index takes time, and consumes resources. If we are accessing the index 5,000 times, it is actually faster to just directly access the table and do a full table scan.

which ressources the author is speaking about here ? shoul I bother about this ? or it's up to the RDBMS to manage all this for me ?

1

That is because SQL Server reads entire pages instead of records and when a index is used SQL Server might need to follow the bookmark to fetch the fields that are not included in the index.

So if your query isn't selective enough, it might end up reading all the pages of the index, and following the bookmarks to read all the pages of the actual table too. This ends up being more expensive than just reading all of the pages of the table.

This effect can be somewhat mitigated using included columns in which some columns are included in the index, but aren't part of the index (i.e. they have no effect on the sorting of the index).

SQL Server estimates the selectivity of your query based on statistics.

This article explains a lot of it. And the stairways series really is a good starting point to understand how indexes work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.