Post reply

Most used Index

  • March 10, 2014 at 10:46 am

    #289220

    Hi Experts,

    Is there anyway to find the most used indexes in a DB?

  • March 10, 2014 at 10:53 am

    #1695661

    Ratheesh.K.Nair (3/10/2014)

    Hi Experts,

    Is there anyway to find the most used indexes in a DB?

    This little DMV ditty should to the trick.

    select

    object_name(s.object_id) as obj_name,

    i.[name] as idx_name,

    last_user_seek, user_seeks,

    last_user_scan, user_scans,

    last_user_lookup, user_lookups,

    last_user_update, user_updates

    from sys.dm_db_index_usage_stats as s

    inner join sys.indexes as i on i.object_id = s.object_id and i.index_id = s.index_id

    order by (user_seeks + user_scans + user_lookups);

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • March 10, 2014 at 10:56 am

    #1695662

    But that's only the "most used" since the last time those values were reset. If the server was restarted last week, a completely different set of indexes might be listed by the same query. There's not a 100% sure way to get this measure, but that one is as close as we can get.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 10, 2014 at 10:59 am

    #1695663

    Thanks a lot Eric and Grant.

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

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