How to obtain SQL Server Index usage

Original article from:

Http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/

Reproduced from: http://blog.csdn.net/dba_huangzj/article/details/7777392

Problem:

The database has two storage requirements: data pages and index pages. Understanding and viewing actual data in your table is quite simple. Run some query examples to learn which columns are used and what types of data are actually stored. On the one hand, it is hard to know how indexes are actually used and used. So how should you better understand the use of indexes and what operations are being performed (inserts, updates, deletes, selects )?

Solution:

In sqlserver, many new dmvs are introduced for you to explore data that is unavailable or hard to see in previous versions. A new function and a new view 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 specific index caused by insert, update, and delete. In addition, this view also provides information about locks, latches, and access methods. It returns many columns, but focuses on the following columns:

• Leaf_insert_count-Total number of leaf-level Inserts
• Leaf_delete_count-Total number of leaf-level Deletions
• Leaf_update_count-Total number of leaf-level updates

The following example shows the key columns for 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)
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 ')
= 1

The following is the output. From this function, we can know how many insert, update, and delete operations occur on each table and index.

SYS. dm_db_index_usage_stats:

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

• User_seeks-Number of index searches.
• User_scans-Number of index scans.
• User_lookups-Number of bookmarked searches.
• User_updates-the number of added, deleted, and modified operations.

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 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 query results, from which you can find the number of searches, scans, bookmarks, and all modifications.

Note:

These values are cleared when sqlserver is restarted. In addition, the value returned by sys. dm_db_index_operational_stats is only valid for metadata objects cached in heap or indexes.