SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Pedro Lopes / usp_IndexesUnused - SQL 2k5 / Latest Posts

RE: usp_IndexesUnused - SQL 2k5

jbuttery — Thu, 26 Feb 2009 11:09:00 GMT

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_NameI'm still researching what ratios of what categories are significant.

RE: usp_IndexesUnused - SQL 2k5

Pedro Lopes MS — Fri, 30 Jan 2009 14:19:31 GMT

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

RE: usp_IndexesUnused - SQL 2k5

don.schaeffer — Fri, 30 Jan 2009 12:50:25 GMT

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.

usp_IndexesUnused - SQL 2k5

Pedro Lopes MS — Wed, 21 May 2008 09:13:43 GMT

Comments posted to this topic are about the item [B]usp_IndexesUnused - SQL 2k5[/B]