SQL Server Performance Analysis-index Efficiency Evaluation

DMV analyzes the index usage, including the number of scans and updates, which helps you evaluate the efficiency of the designed index. SQL Server 2005 is indeed much higher than 2000 in performance analysis.

User_seeks: the number of searches executed by the user.
My understanding: This counts the number of times the index is seek

User_scans: queries the number of scans performed by the user.
My understanding: the number of scans in this statistical table, no index matching
User_lookups: the number of queries executed by the user.
Personal Understanding: the number of times the user uses the RID or clustered index to search for data through index search. For the data in the heap or clustered table
Times used with indexes
User_updates: the number of updates executed by the user query.
My understanding: the number of index or table updates

 

--- Use few indexes to rank first
Declare @ dbid int
Select @ dbid = db_id ()
Select objectname = object_name (S. object_id), S. object_id, indexname = I. Name, I. index_id
, User_seeks, user_scans, user_lookups, user_updates
From SYS. dm_db_index_usage_stats,
SYS. Indexes I
Where database_id = @ dbid and objectproperty (S. object_id, 'isusertable') = 1
And I. object_id = S. object_id
And I. index_id = S. index_id
Order by (user_seeks + user_scans + user_lookups + user_updates) ASC