Index Usage Monitoring

Database consists of two types of pages Index pages and Data pages.By running up of some sample queries we can come to conclusion that what data in the table and what type of data is being populated on to the table on other hand it is little bit hard to find out which index is being mostly using for DML operation and which index is being used for Scans and Seeks.

Earlier Version's of sql server don't have an opportunity to gain the performance of Dynamic Management Views.One function and View can give a lot of in depth data about the indexes.

                                        Function:  sys.dm_db_index_operational_stats

                                                   lets see the usage of this function

Dynamic View: sys.dm_db_index_usage_stats

Now let see the usage of this view