MS SQL Server 2008 Index Option

The sys.dm_db_index_usage_stats dynamic management view allows you to track which index is most frequently used for update and insert operations. Using this view, you can track when an index was last used. This view also tracks the type of indexes used for update and insert operations. Every time an index is used for an operation such as an update, the counter of the index in the view is incremented by one. This
view is also used for tracking unused indexes, which typically cause maintenance overhead.If you have a performance-related concern, you can use the Database Engine Tuning Advisor to recommend indexes for a single or multiple databases. The Database Engine Tuning Advisor can work with different types of indexes, such as clustered or non-clustered , and to resolve unused or potential index issues.

The sys.dm_db_index_physical_stats function is a replacement of the DBCC SHOWCONTIG statement from the previous version of SQL Server. This function returns the defragmentation of indexes and data. The indexes and data can be part of a specific view or a table. This function will not be able to track indexes that are most frequently used for update and insert operations, and therefore should not be used.

The sys.dm_db_index_operational_stats function can be used to obtain details of a partition of a table or about an index. This function will not be able to track indexes that are most frequently used for update and insert operations, and therefore should not be used.

The sys.dm_fts_index_population view displays only the full-text indexes that are currently in use. Any full-text index that is not currently being used is not displayed in this view. This view will not be able to track indexes that are most frequently used for update and insert operations, and therefore should not be used.