usp_IndexesUnused - SQL 2k5

  • Pedro Lopes MS

    SSChasing Mays

    Points: 612

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

  • don.schaeffer

    Ten Centuries

    Points: 1165

    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

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

    Thank you 😎

  • jbuttery

    Ten Centuries

    Points: 1196

    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.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply