Index usage statistics in SQL Server

How to check index usage statistics in sql server ?

There are two ways to check the index usage statistics.

• Canned Reports
• DMV Query

Canned Report to get index usage

Login to SQL Server 

Right Click on the database on which you table and index present.

Now navigate to standard report and click on the Index usage statistics report as shown below.

After click on Index usage statistics your report will load.

DMV query to get index usage statistics.

Select

(dense_rank() over (order by s.name,t.name))%2 as l1

,       (dense_rank() over (order by s.name,t.name,i.name))%2 as l2

, s.name as [schema_name]

,       t.name as [table_name]

,       i.name as [index_name]

,       i.type_desc

,       iu.object_id 

,       iu.index_id 

,       iu.user_seeks  as seek_user

,       iu.user_scans as scan_user

from sys.dm_db_index_usage_stats iu

inner join sys.indexes i on  ((iu.index_id = i.index_id) and (iu.object_id = i.object_id))

inner join sys.tables t on ( i.object_id = t.object_id )

inner join sys.schemas s on (s.schema_id = t.schema_id)

where iu.database_id = db_id() and i.type <> 0

order by s.name,t.name,i.name