Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Index Selectivity Expand / Collapse
Posted Thursday, May 1, 2008 11:34 AM


Group: General Forum Members
Last Login: Thursday, January 10, 2013 10:30 AM
Points: 117, Visits: 424

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 ..

Post #493804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse