On index statistics

If an index is not being used much, it will have a lot of free space in the index pages, thus occupying a lot more disk space then it should and it may be worth compacting it (via rebuild or defrag) to get the disk space back. At the same time is an index is barely used, there might not be a point to spend resources to remove any kind of fragmentation.

Another thing is a non-clustered index affects performance in a number of ways, one of them being the amount of extra IO it takes to maintain each index. For example whenever a new record is added to the database, a matching record is inserted into a non-clustered index, or whenevr the record is deleted, a matching record for the index must be deleted as well, or a clustered index's value is changed during update, the matching record for non-clustered index must be updated as well.

There is a dynamic management view sys.dm_db_index_usage_stats that can help us determine whether a particular index is being used. This view does not keep historical data but keeps the data only since the last time database was open. It does not keep the data after the database has been closed.

The sql below will retrieve data for a particular database

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('DatabaseName')

Below are some useful fields from sys.dm_db_index_usage_stats view:

user_seeks - either looking up a single row or doing a range scan
user_scans - select * operation performed on the table
user_lookups - a bookmark lookup where a non-clustered index does not fully cover a query and additional columns must be retrieved from the base table row
user_updates - number of times it was used in update operations (this included inserts, updates, and deletes)

Some useful queries:

--get database name based on database_id
select * from sysdatabases where dbid=7

--get object name based on object_id
SELECT name
FROM sys.objects
WHERE object_id = 37575172

--retrieves database_id of a current database
Select db_id()

--retrieves an index name based on object_id and index_id from sys.dm_db_index_usage_stats view
SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id

--retrieves index statistics, including table name to which the index belongs, and index name
SELECT object_name(iu.object_id, iu.database_id) as tablename, i.name as IndexName, iu.user_seeks,iu.user_scans,iu.user_lookups,iu.user_updates,iu.last_user_seek, iu.last_user_scan,iu.last_user_lookup,iu.last_user_update 
FROM sys.dm_db_index_usage_stats as iu
LEFT JOIN sys.indexes as i ON iu.index_id=i.index_id AND iu.object_id=i.object_id
WHERE iu.database_id = DB_ID('Northwind')

The query below will return a lit of all indexes with corresponding table names that have not been used by SQL Server since the service started

SELECT  ObjectName = OBJECT_NAME(ind.object_id),  IndexName = ind.name
FROM Sys.Indexes ind            
INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE OBJECTPROPERTY(obj.object_id,'IsUserTable') = 1            
AND NOT EXISTS ( SELECT 1 FROM Sys.dm_db_index_usage_stats usg WHERE usg.object_id = ind.object_id AND usg.index_id = ind.index_id AND ind.index_id = usg.index_id)
ORDER BY ObjectName,IndexName