question

Varun avatar image
Varun asked

SQL Server 2005 DMV for indexes

In SQL 2005 there are # of system views to view information related to indexes, i am wondering if there is any system dmv that stores the usage of index, information like how often a index is used and when a index is last used or if there are any indexes in a database that are never used before?

indexesdmvdynamic-management-view
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

There are loads and I think the way to use them is pretty much beyond the scope of an answer here. I have listed some of the most useful in most cases below. I would suggest you search for blog articles or BOL for explanations of how they can be combined to provide information on how your indexes are being used and how you might adjust them to improve your server and database performance.

Existing index data is stored in:
dm_db_index_operational_stats
dm_db_index_physical_stats
dm_db_index_usage_stats

information regarding indexes that might be created to give a potential performance improvement: dm_db_missing_index_columns
dm_db_missing_index_details

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image Rob Farley commented ·
Yup. But you should also learn about when indexes that don't seem to be used actually could be.
2 Likes 2 ·
Kev Riley avatar image
Kev Riley answered

Here's an excellent example of how to use the missing indexes DMVs, and one for unused indexes too.

There are lots more available on SSC too.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
thanks for finding some links, I have some on my office PC, not here at home...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.