Optimize index structure: Which indexes are not used in my database? SQL 2005 Index usage statistics and disk space usage.

In SQL 2005 a lot information is stored about how indexes are used and which indexes can be useful to create. This is the first post in a serie about optimzing your index structure.

In SQL 2005 you can retrieve index usage statistics of all indexes in your database. This is a really good feature which allow you to tune your index structure of your database. Every database is used on it's own way. For a standard software package like Exact Globe, an index structure is shipped with the product. This index structure is not always optimal. It depends on how you are using the software. For instance, an index for costunits is only useful if you make use of costunits in your application.
With the index usage statistics you can see how much times an index is used. The most easiest ones are the indexes which are never used for reading but are only updated.

This script will display the index usage statistics for the table 'Gbkmut' which are never read.

-- Used Indexes with only updates and No reads
SELECT object_name(s.object_id) AS ObjectName,ISNULL(i.name, '(Heap)') AS IndexName,
user_seeks AS IndexSeeks,user_scans AS IndexScans,user_lookups AS LookUps,
user_seeks + user_scans + user_lookups AS TotalReads,user_updates AS Updates,
CASE WHEN (user_seeks + user_scans - user_updates < 0) THEN'Updates > Reads'
WHEN (user_seeks < user_scans) THEN'Scans > Seeks'
ELSE' '
END AS Warning
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE database_id = DB_ID() AND objectproperty(s.object_id,'IsUserTable') = 1
AND object_name(s.object_id) = 'gbkmut'
AND (user_seeks + user_scans + user_lookups)=0

The result set of this query are indexes which maybe can be deleted. Monitor these indexes for a period in which you know that all functionality in your software packages is used.

Please be aware that index statistics are stored in Dynamic Views. After a restart of SQL Server the index usage statistics are reset to 0. Index statistics for a table are also reset to 0 when index changes are executed on this table.

With next query you can see how much diskspace the indexes for table 'Gbkmut' are using.
-- Indes sizes of an table
SELECT Name as Index_name, Type_desc AS IndexType, Space_used_in_mb = (page_count * 8.0 / 1024.0)
FROM SYS.INDEXES I
INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(db_id(), object_id('gbkmut'), null, null, null) IP
ON I.object_id = IP.[object_id] and I.[index_id] = IP.[index_id]
ORDER BY I.index_id