How to obtain SQL Server index usage

There are two major storage needs for a database: data pages and index pages. It is fairly straightforward to understand and view the actual data in your table . Run some query examples to see which columns are used and what type of data is actually stored. On the one hand, it is difficult to know how the index is actually used and how it is used. So how do you better understand the use of indexes and what is happening (inserts,updates,deletes,selects)?

In SQL Server, many new DMVs are introduced to allow you to spy on data that is not available or difficult to see in previous versions. One of the new functions and a new view is to provide index usage:

Sys.dm_db_index_operational_stats and Sys.dm_db_index_usage_stats

Sys.dm_db_index_operational_stats:

This function provides information about the insert/update/delete that is generated on a particular index. In addition, this view also provides information about locks, latches, and access methods. It returns many columns, but focuses on the following columns:

• Total number of leaf_insert_count– leaf level inserts
• Leaf_delete_count-Total number of leaf level deletions
• Leaf_update_count-Total number of leaf level updates

The following example queries provide some key columns for useful information on adding, deleting, and modifying operations:

select object_name ( a.[object_id] ) as [OBJECT NAME] ,
       i.[name] as [INDEX NAME] ,
        a.leaf_insert_count ,
       A.leaf_update_count ,
       a.leaf_delete_count
from    SYS. Dm_db_index_operational_stats (null,null,null,null) A
       INNER JOIN SYS. INDEXES as I
         on i.[object_id] = a.[object_id]
            and I. index_id = a.index_id
where  objectproperty ( a.[object _ID] , ' isusertable '/span> ) = 1

Here is the output, from which we can learn how many insert/update/delete operations occur on each table and index.

Sys.dm_db_index_usage_stats:

This view provides an overview of the access methods for your index, and the following columns are useful for checking the problem:

• Number of user_seeks– index lookups.
• Number of user_scans-index scans.
• Number of user_lookups– bookmark lookups.
• user_updates– the number of changes to the operation.

select   object_name ( s.[object_id] ) as [OBJECT NAME] ,
         i.[name] as [ INDEX NAME] ,
         user_seeks ,
          User_scans ,
          user_lookups ,
         user_updates
from     SYS. Dm_db_index_usage_stats as S
         INNER JOIN The SYS. INDEXES as I
           on i.[ OBJECT_ID] = s.[object_id]
               and i.index_id = s.index_id
where    objectproperty ( s.[object_ ID] , ' isusertable ' ) = 1

The following are the results of the query, from which you can learn how many find, scan, bookmark lookup, and all modifications are done.

These values are emptied when SQL Server restarts, and the value returned by Sys.dm_db_index_operational_stats is only valid for metadata objects cached in the heap (heap) or index.