When building an application there are a number of different things that are important. One of those is having a good database design. You want a database structure that is easy to use and query. Another factor that is important is how well it performs. When I mean performs it is how quickly your applications are able to retrieve and update the data they desire. One of the key components of SQL Server that allows your queries to return data quickly is the indexes you place on your tables. With good indexes that are appropriate for your application the database engine can minimize the amount of work needed to return your data. On the flip side of that if your database has to many indexes updates and inserts into your database might perform poorly while SQL Server spends CPU cycles and I/O’s update all the necessary indexes. In this article I will discuss how to identify which indexes you are using and those you are not, as well as how those indexes are being used.

This information can then be used to tune your indexes to optimize your database design.

How to Obtain Index Usage Statistics
With SQL Server 2005 Microsoft introduced Dynamic Management views (DMVs) and Functions (DMFs). With these new DMVs and DMFs you can get at a wealth of information into how the SQL Server engine is performing and using resources. One such DMV that provides you some great index usage statistics is sys.dm_db_index_usage_stats.

As the name suggests, this view allows you to some statistics about how indexes are used. This DMV keeps statistics for indexes in all databases. For each index that has been used the DMV reports has a number of counters to identify how the index was used, as well as some dates that identify when an index was last used. You are able to use the index usage statistics to determine how useful an index really is. By using the information returned from this view we determine if an index is improving or hindering our application performance.

In order to use this DMV you will need to have VIEW SERVER STATE permissions. If you can’t run the queries in the article due to permission problems please talk to your local DBA and asked for the permissions. Hopefully they will allow you to have the right you need to run these queries, if not then asked them to run these queries for you. I’m sure after you asked for these to be run a few time they might reconsider giving you access, so you are not routinely asking for them executing these queries.

How does tSQL Server gather statistics for this DMV
SQL Server keeps track of each time an index is used, and how it is used each time a T-SQL statement is executed. For each statement executing, SQL Server using the query plan information to incrementing the appropriate counter columns in the “sys.dm_db_index_usage_stats” DMV. The index count information is an accumulated count based on how each indexes has been used since SQL Server was last started, the index was created, the database was attached or the last time the database was started (if AUTOCLOSE option is ON). Keep in mind the usage counters are not the number of I/O or PAGE read, but just the number of time the index was used. When looking at index using statistics using the sys.dm_db_index_usage_stats DMV you need to keep this information in mind. The longer SQL Server has been collection statistics the more likely the statistics will be an accurate representation of how your application or applications are taking advantage of your indexe

Indentifying Indexes that are not being used
Having indexes in your databases that are not being used by your application, is like having an eight bedroom house, but only using 3 of them. Having a eight bedroom house might be nice if you can afford one, but you are paying and maintaining 5 bedrooms you don’t need. If you never use these extra 5 bedrooms you mostly are still heating them in the winter and air conditioning them in the summer, and worst of all you are paying additional property tax for that extra space you never use. These maintenance cost is just money you are throwing away each year. Having extra indexes on you tables that are not being used still require SQL Server to maintain them every time an update occurs. They also they take up additional space if they are never used. So like the extra bedrooms, these extra indexes are costing you slower response for updates, and longer backup times to backup that unused index storage. So how do you identify those indexes that are not used?

Here is a T-SQL select statement that uses this DMV to identify all the indexes that have not been used, for the AdventureWorks database:

USE AdventureWorks;
go
SELECT o.name Object_Name,
i.name Index_name,
i.Type_Desc
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
LEFT OUTER JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = 'u'
-- Clustered and Non-Clustered indexes
AND i.type IN (1, 2)
-- Indexes without stats
AND (s.index_id IS NULL) OR
-- Indexes that have been updated by not used
(s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );


This T-SQL statement using the “user seeks”, “user_scans” and or “user_looksups” columns in the sys.dm_index_usage_stats to identify whether or not this an index has been used. This information is then joined with the sys.objects, and sys.indexes tables in the AdventureWorks database to identify index usage information for only the AdventureWorks database. If the counters mentioned above are zero for an index in the database then this means the index has not been used. If any one of these counters have a non-zero number then this would indicate that that index has been used.

Capturing Indexes That Have Been Used
Alternatively you might want to capture all the indexes that have been used on a daily bases and store them in a file. Then after a few months you could use the information you collected to determine which indexes have not been used. Here is another code snippet that show those indexes that have been used:

USE AdventureWorks;
go
SELECT o.name Object_Name,
SCHEMA_NAME(o.schema_id) Schema_name,
i.name Index_name,
i.Type_Desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = 'u'
-- Clustered and Non-Clustered indexes
AND i.type IN (1, 2)
-- Indexes that have been updated by not used
AND(s.user_seeks > 0 or s.user_scans > 0 or s.user_lookups > 0 );


When I run this on my instance of SQL Server I get the following output:

Full article...