Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

usp_IndexesUnused - SQL 2k5 Expand / Collapse
Author
Message
Posted Wednesday, May 21, 2008 9:13 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 7, 2012 9:04 AM
Points: 84, Visits: 144
Comments posted to this topic are about the item usp_IndexesUnused - SQL 2k5
Post #504567
Posted Friday, January 30, 2009 12:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:03 PM
Points: 71, Visits: 832
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.
Post #647272
Posted Friday, January 30, 2009 2:19 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 7, 2012 9:04 AM
Points: 84, Visits: 144
And you're right, of course. I'll edit ASAP.
Thank you
Post #647335
Posted Thursday, February 26, 2009 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:40 AM
Points: 46, Visits: 133
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.


Post #665186
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse