SQL Server Performance

Low cardinality of index

Discussion in 'Performance Tuning for DBAs' started by dtipton, Mar 7, 2003.

  1. dtipton New Member

    I maintain a database serving as a backend for a vendor supplied application purchased by my department. The majority of activity (90%) consists of update/insert statements against a single table that contains 14 million rows (pointers to images on an optical jukebox).

    The table has 4 indexes defined, but two of them(non-clustered) have only two distinct values one of which is NULL. With such low cardinality, I doubt the optimizer will ever make use of them and considering the type of activity (80% update/insert) that's a chunk of overhead for something that won't be used.

    The users are complaining about system performance.

    I captured the update and occasional select statements being issued and their execution plan indicates that they are accessing the table using an index seek on the clustered index defined on the table in question.

    Can anyone think of a reason to keep the other two indexes around?

  2. bradmcgehee New Member

    Based on what you have described, when you only have two distinct values in a column, indexes are worthless. I would dump them. Of course, after doing so, I would watch performance carefully to see if there was something that was overlooked, and if so, be in a position to readd them if need be.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. rajib New Member

    In Oracle for the low cardinality colums DBA can use bitmap indexes, is there any way for SQL Server for low cardinality columns? Does Low selectivity and low cardinality means same?
  4. techbabu303 New Member

    Not yet may be in future releases.
    If you mean a query with cardinal function with equals = sign like, then it means the same.
    select * from table where a=1

    Some additional references
    http://sql-server-performance.com/articles/per/indexing_low_sel_cols_p2.aspx
    -Sat

Share This Page