Index Usage Query Using DMV's

SELECT
obj.name AS TableName
, idx.name AS IndexName
, ius.index_id
, ius.user_seeks
, ius.user_scans
, ius.user_lookups
, ius.user_updates
,  ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates AS TotalUserUsages
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
, idx.fill_factor
, idx.is_padded
, idx.is_primary_key
, idx.is_disabled
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.objects obj
ON ius.object_id  = obj.object_id 
INNER JOIN sys.indexes idx
ON ius.object_id  = idx.object_id 
AND ius.index_id = idx.index_id
--WHERE obj.name = '[table name]'
--This will find indexes without user usage
WHERE ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates = 0

ORDER BY is_disabled, index_id

Defining an Index for the Rest of the World

Picture a data as just a pile of information like all the chapters of a book on the floor in a pile.

Indexes are like the indexes of a book

1. One usually puts the book in the order the author intended (Primary Key)
2. Several other might order specific things like give me a way to find the first page a character appears on or all the fight scenes. (indexes)

Each of these allow you to either get a collection of pages or a specific value without having to start at page one and make a list of the values you want. For example, I want to know when Chad is first mentioned in a book. Without the index I have to start at page one of the book and read it until I find chad even if he is only on the last page. With the index of characters I would call up a list of the characters by name and it would tell me the page number. As you can see the lookup took a lot less time and fewer resources.

That is the good. In life nothing is free. For the index to be any good it has to be maintained when data changes especially inserts and deletes. Every index slows the time for these down. The key then is to only add indexes that have value to what the users are doing. Therefore we look at how often it is used before making a choice. For example, if you are only going to look for Chad once when you read the book the first time there is no value in making and maintaining a list of character entry points so you wouldn’t waste time with it.