Determining indexes utilization Or unused indexes

You can use following DMV to check index utilization or to see unused indexes.

SYS.DM_DB_INDEX_USAGE_STATS

By determining the stats, you can see what indexes are mostly used by the queries and what indexes are unused and overhead for the system from maintenance point of view. These stats/counters will be initialized to empty whenever SQL Server restarts, database is de-attached, or database is shutdown.

When you perform SELECT, INSERT, UPDATE or DELETE operations, then this DMV keeps track all of your performed operations in each separate counter as:

USER_SCANS: selection performed on whole table without specifying any criteria. (SELECT * operation)

USER_SEEKS: selection performed by specifying any criteria. (Either looking up a single or doing range scan)

USER_LOOKUPS: large selection performed with specified criteria by using non-clustered indexes.

USER_UPDATES: level of maintenance when INSERT, UPDATE or DELETE operation performed.

Above counters have associated dates, these are updated when above any counter incremented. This means this date will reflect about the last date of action performed or last time when index was accessed.

LAST_USER_SCAN
LAST_USER_SEEK
LAST_USER_LOOKUP
LAST_USER_UPDATE

You can use the following query to get index name and its associated user stats.

SELECT SI.INDEX_ID,SI.NAME,USER_SCANS,USER_SEEKS,USER_LOOKUPS, USER_UPDATES, LAST_USER_SCAN, LAST_USER_SEEK, LAST_USER_LOOKUP,LAST_USER_UPDATE
FROM SYS.DM_DB_INDEX_USAGE_STATS DMI
INNER JOIN SYS.INDEXES SI ON SI.INDEX_ID=DMI.INDEX_ID AND DMI.OBJECT_ID=SI.OBJECT_ID
WHERE DMI.OBJECT_ID=OBJECT_ID('TEST')

Example:

1) Create test table
CREATE TABLE TEST
(VAL1 INT IDENTITY(1,1)
,VAL2 VARCHAR(2)
,VAL3 VARCHAR(3)
)

2) Create Indexes
CREATE CLUSTERED INDEX VAL1_INDEX ON TEST (VAL1)
CREATE NONCLUSTERED INDEX VAL2_INDEX ON TEST (VAL2)
CREATE NONCLUSTERED INDEX VAL3_INDEX ON TEST (VAL3)

3) Insert test data

DECLARE @I INT
SET @I=1
WHILE (@I<100000)
BEGIN
INSERT INTO TEST VALUES('C','B')
SET @I=@I+1
END

4) Check USER_UPDATES and LAST_USER_UPDATE values

5) Update 50% data with new values

UPDATE TEST SET VAL2='D',VAL3='F' WHERE VAL1>50000

6) Check values for USER_UPDATES, LAST_USER_UPDATE, USER_SEEKS, LAST_USER_SEEK

7) SELECT * FROM TEST

8) Check values for USER_SCANS and LAST_USER_SCAN

9) SELECT * FROM TEST WHERE VAL1=40

10) Check values for USER_SEEKS and LAST_USER_SEEK

11) SELECT * FROM TEST WHERE VAL2='F'

12) Check values for USER_SEEKS, LAST_USER_SEEK, USER_LOOKUPS and LAST_USER_LOOKUP