usp_IndexesUnused - SQL 2k5

• Pedro Lopes MS

  SSChasing Mays

  Points: 612

  More actions

  May 21, 2008 at 9:13 am

  #182689

  Comments posted to this topic are about the item usp_IndexesUnused - SQL 2k5

• don.schaeffer

  Ten Centuries

  Points: 1245

  More actions

  January 30, 2009 at 12:50 pm

  #935803

  The last line of the where clause reads:

  AND ((a.user_seeks + a.user_scans + a.user_lookups) = 0 OR a.user_updates = 0)

  I think "OR a.user_updates = 0" should be removed. For example, I've got an index with 9 million hits but no updates. This index is certainly not unused just because the data is static.

• Pedro Lopes MS

  SSChasing Mays

  Points: 612

  More actions

  January 30, 2009 at 2:19 pm

  #935854

  And you're right, of course. I'll edit ASAP.

  Thank you

• jbuttery

  Ten Centuries

  Points: 1226

  More actions

  February 26, 2009 at 11:09 am

  #950730

  I'd like to see some type of formula that would determine the efficiency of a particular index, e.g. (pseudo-code)

  If (user_seeks <> '0') and (user_scans/user_seeks > 0.5)

  output 'Verify Index' , Table_Name, Index_Name

  I'm still researching what ratios of what categories are significant.