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?
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:
information regarding indexes that might be created to give a potential performance improvement: dm_db_missing_index_columns
answered Mar 28 '10 at 05:52 AM