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:

dmv2

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

dmv1

kick it on DotNetKicks.com

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ 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: