70-432 : Index usage (sys.dm_db_index_usage_stats)

This blog article is aimed at people preparing for 70-432 exam.

This DMV is particularly useful and represents one area where SQLServer appears to be ahead of te Oracle database engine (there is no easy to see which indexes have not been used in the last day / week / month ...)

Starting with the msdn documentation for dm_db_index_usage_stats:

sys.dm_db_index_usage_stats (Transact-SQL)

Returns counts of different types of index operations and the time each type of operation was last performed.

Column name	Data type	Description
database_id	smallint	ID of the database on which the table or view is defined.
object_id	int	ID of the table or view on which the index is defined
index_id	int	ID of the index.
user_seeks	bigint	Number of seeks by user queries.
user_scans	bigint	Number of scans by user queries.
user_lookups	bigint	Number of bookmark lookups by user queries.
user_updates	bigint	Number of updates by user queries.
last_user_seek	datetime	Time of last user seek
last_user_scan	datetime	Time of last user scan.
last_user_lookup	datetime	Time of last user lookup.
last_user_update	datetime	Time of last user update.
system_seeks	bigint	Number of seeks by system queries.
system_scans	bigint	Number of scans by system queries.
system_lookups	bigint	Number of lookups by system queries.
system_updates	bigint	Number of updates by system queries.
last_system_seek	datetime	Time of last system seek.
last_system_scan	datetime	Time of last system scan.
last_system_lookup	datetime	Time of last system lookup.
last_system_update	datetime	Time of last system update.

Remarks

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.

http://msdn.microsoft.com/en-us/library/ms188755.aspx

Now the following is a good blog post with an example of how this works in practice and also when you should be checking index usage stats. Not only does the following provide a nice simple of example of usage of the DMV sys.dm_db_index_usage_stats but it also makes a good point about focusing your attention and efforts on defragmentating the key indexes with high usage stats (it is too easy to get side tracked by problems where you can see something is clearly wrong, but you aren't necessarily tackling the key performance problems) :

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’.

If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a lot more disk space than it could do and it may be worth compacting (with a rebuild or a defrag) to get that disk space back. However, usually there’s not much point spending resources to remove any kind of fragmentation when an index isn’t being used. This is especially true of those people who rebuild all indexes every night or every week.

...

If you're interested in whether an index is being used, you can filter the output. Let's focus in on a particular table - AdventureWorks.Person.Address.

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

You'll probably see nothing in the output, unless you've been playing around with that table. Let's force the clustered index on that table to be used, and look at the DMV output again.

SELECT * FROM AdventureWorks.Person.Address;

GO

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

Now there's a single row, showing a scan on the clustered index. Let's do something else.

SELECT StateProvinceID FROM AdventureWorks.Person.Address

WHERE StateProvinceID > 4 AND StateProvinceId <>

GO

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

And there's another row, showing a seek in one of the table's non-clustered indexes.

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/20/how-can-you-tell-if-an-index-is-being-used.aspx