Index Usage SQL Server

--Check if index for the table is being used.

SELECT i.index_id, i.name, u.user_seeks, u.user_lookups, u.user_scans

FROM sys.dm_db_index_usage_stats u

INNER JOIN sys.indexes i ON

u.object_id = i.object_id AND

u.index_id = i.index_id

WHERE u.object_id=object_id('SchemaName.TableName')

order by index_id

--Query to get last Index/Table update, insert and deletes

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 (db_id(),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') = 1

order by a.leaf_insert_count desc

--Query to find Index usage Read Vs Write. If a index is used a lot more for Write operation, than it should be analysed to see if it is covering index for insert/update/delete. if not it may be causing overhead for write operations.

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,

user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],

user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND user_updates > (user_seeks + user_scans + user_lookups)

AND i.index_id > 1

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);