Index Selectivity

• aruram

  SSCrazy

  Points: 2379

  More actions

  May 1, 2008 at 11:34 am

  #121932

  Hi,

  I would like to know if the sql 2005 table SYS.DM_DB_INDEX_USAGE_STATS can be used to get an accurate estimate of the usefulness of an index ..

  For eg, we have 2 production tables (19 + million rows and 27 + million rows each) with a fairly large clustered index and multiple NC indexes and are looking at opportunities to drop unused NC indexes and increase free space and performance

  It is safe to assume that the NC indexes that have counts in the single or 2-digits are not useful at all and can be dropped.

  I am more concerned about the indexes that show some usage (having counts between 1000-2000) .. which is still not a big %age of the entire data.

  Is there a way to accurately calculate the usefulness of the indexes just looking at the usage_stats table and have a cut off point to determine if an index can be dropped or not ? thanks ..