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?

asked Mar 27 '10 at 11:34 PM in Default

Varun
86 ● 4 ● 4 ● 6

(comments are locked)

2 answers:

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

answered Mar 28 '10 at 05:52 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

Yup. But you should also learn about when indexes that don't seem to be used actually could be.

Mar 28 '10 at 06:23 AM Rob Farley

excellent point Rob. I should have mentioned that too, my only possible excuse is that we just lost our daylight savings hour and I've only had one coffee so far today!

Mar 28 '10 at 06:41 AM Fatherjack ♦♦

+1 Very informative response.

Mar 28 '10 at 01:00 PM CirqueDeSQLeil

(comments are locked)