Finding Index usage

When troubleshooting performance problems, a typical questions is: "which indexes can be disabled on SQL"? This blog shows a simple way to get some ideas of which indexes are used. The method will give some ideas of both how often they are updated (causing overhead), and how often the indexes are actually used. So it gives some ideas of costs and benefits for each index.

SQL2005 introduced the concept of Dynamic Management Views (DMVs), which show internal statistics from a lot of different areas in SQL Server. The DMVs can all be found in SQL Server Management Studio, if you expand System Databases -> master -> Views -> System Views. I would recommend to take a quick look, and get an idea of what other useful information is available here.

The view relevant to this blog is called sys.dm_db_index_usage_stats. To run it, just do a SELECT on this view. Or, to get results that are a bit more useful, run this:

SELECT
db_name(database_id),
object_name(object_id),
* FROM sys.dm_db_index_usage_stats

Check the columns user_seeks, user_scans and user_lookups to get an idea how often an index is being used. Then compare with the column user_updates, to see how often the index is being used.

There are a number of other columns available, and the query above could easily be modified to for example ORDER BY user_updates, to see the indexes causing the largest overheads, and then check the actual usage of these indexes.

Note: The DMV will b e reset every time you restart SQL Server. So the number is shows only represent index usage since last time you restarted SQL Server.

This posting is provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.