Skip to content

How To: Finding Index Usage Stats With DMV

November 9, 2008

DBA’s utilize indexes as one way of optimizing the performance of their queries. They either create or drop indexes according to the current needs of their system. Using the Dynamic Management Views (DMV) and the other related catalog views, DBA’s are able to analyze the statistics for indexes.

In the following figure, we use TSQL to find the stats for the user_seeks, user_scans, user_lookups, and the related objects tied to the index:


The query above will give us the following (selected only the top 5 for the purpose of the post)


kick it on

One Comment leave one →
  1. schep021 permalink
    April 16, 2010 8:02 AM

    For the join, I added another parameter where the object_ids match:

    join sys.indexes as i
    on u.index_id = i.index_id and u.object_id = i.object_id

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

  • Archives

  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: