Last week I blogged about how to get help identifying possible missing indexes by using some of the DMV’s provided by SQL Server. This week I will show another one that can be used to see how often a given index has been used.

The DMV is this one:

select * from sys.dm_db_index_usage_stats

This view contains information about the indexes that have been used since the last SQL Server restart, or since the last time a given database became online. After a service restart, no rows will be returned by the select statement above. Each time a new index is accessed, a row will be added to the output. If an index is accessed again, the values of the output will be updated.

The DMV contains database_id, object_id and index_id, so you are free to filter on specific databases, objects or even indexes. Let me show a little demo:

USE GeniiiusIndexUsage
GO
CREATE TABLE HeapTable (id int identity, val int)
GO
INSERT INTO HeapTable (val) values (1)
GO
INSERT INTO HeapTable (val) values (2)
GO
SELECT *
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id('GeniiiusIndexUsage')

I create a simple table without a clustered index, and I insert two rows. The output of the SELECT against the DMV is this:

I can see that index_id = 0 (that’s the heap) has had 2 user_updates corresponding to my insert statements. Now let me try to do a select against the table:

SELECT * FROM HeapTable
SELECT *
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id('GeniiiusIndexUsage')

The restult  now shows 1 in the user_scans column:

And it’s not hard to imagine what would happen if you have an index and performs a lookup against it. I’ll skip the demo of this.

So, what can this be used for?
First of all it can give you some insight to which indexes are used most often, and you can calculate the read/write ratio by using the user_seeks, user_scans and user_updates columns. If you for instance find an index that have been written to 1000 times, but never read from (user_seeks = 0 and user_scans = 0) – do you then really need this index? We cannot say for sure, but you should definately keep an eye on such an index over time, and then consider if  you should drop it.

What if an index does not show up in this DMV?
Because rows are first added to the DMV the first time an index are used, every index that does not appear in the DMV, has not been used since last time the database came online. Let’s see how you can find such indexes:

I’ll start by creating an index, but not use it, and then join sys.indexes with sys.objects and sys.dm_db_index_usage_stats:

USE GeniiiusIndexUsage
GO
CREATE NONCLUSTERED INDEX IX_HeapTable_val ON HeapTable(val)
GO
SELECT
    obj.name as TableName,
    ix.name as IndexName,
    ix.type_desc,
    ix.is_unique,
    ix.is_primary_key
FROM
    sys.indexes ix
    INNER JOIN sys.objects obj ON ix.object_id = obj.object_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ix_usage
        ON ix.object_id = ix_usage.object_id AND ix.index_id = ix_usage.index_id
WHERE
    obj.is_ms_shipped = 0
    AND ix_usage.index_id IS NULL

The result is:

The query returns the new index IX_HeapTable_val that I just created. Because this query joins sys.objects and sys.indexes, it will only return unused indexes in the scope of the current database.

Again you cannot be 100% sure that this index could/should be dropped, but it is a definately candidate to watch over time. In my case, it could be that I will start using this new index when a new batch job will run next week, and in that case it would be stupid to drop it. So it takes some knowledge about your setup to be able to decide whether or not an index could safely be dropped.

Why drop unused indexes?
If you have a lot of completely unused indexes, the only down side is a total waste of space. Your backups will be larger than needed, and your restore time will be longer than needed.
If you have a lot of indexes that are written to, but never read from, then you experience an overhead on all inserts/updates/deletes on the given table, putting extra load on your disk subsystem, and slower response times for your users.

As I wrote in the last blog post about missing indexes, these queries does not give you the answer to everything, but it will certainly help you spot those old unused indexes for your database spring cleanup task.

@HenrikSjang