usp_IndexesUnused

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Pedro Lopes » usp_IndexesUnused - SQL 2k5

usp_IndexesUnused - SQL 2k5

Rate Topic

Display Mode

Topic Options

Author

Message

Pedro Lopes MS

Posted Wednesday, May 21, 2008 9:13 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 07, 2012 9:04 AM
Points: 84, Visits: 144

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

Post #504567

don.schaeffer

Posted Friday, January 30, 2009 12:50 PM

Valued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:56 PM
Points: 60, Visits: 634

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

Pedro Lopes MS

Posted Friday, January 30, 2009 2:19 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 07, 2012 9:04 AM
Points: 84, Visits: 144

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

Post #647335

jbuttery

Posted Thursday, February 26, 2009 11:09 AM

SSC 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 »

Permissions