THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

sys.dm_db_index_usage_stats

This object gives statistics on how an index has been used to resolve queries. Most importantly it tells you the number of times a query was used to find a single row (user_seeks), a range of values, or to resolve a non-unique query (user_scans ), if it has been used to resolve a bookmark lookup (user_lookups) and how many changes to the index (user_updates. Note that sys.dm_db_index_operational_stats will give the details of how it has been modified.)

It returns all indexes (including heaps and the clustered index) from the entire server for each index that has been used, though you will usually only want to use it for one database since you will have to look up the name of the index in sys.indexes. There will not be a row in sys.dm_db_index_usage_stats unless the index has been used since creation or since SQL Server has been restarted.

Type: view

Data: accumulating, refreshed when server is restarted or (perhaps obviously) when the index is dropped and recreated. Statistics live on when the index is rebuild, reorganized, and even when it is disabled and rebuilt.

Columns:

  • database_id – primary key of the database
  • object_id - the object_id of the table the index belongs to
  • index_id - the primary key of the index
  • user_seeks – the number of times the index has been used in a user query in a seek operation (one specific row)
  • user_scans – the number of times the index has been used by scanning the leaf pages of the index for data
  • user_lookups – for clustered indexes only, this is the number of times the index has been used in a "bookmark lookup" to fetch the full row. This is because non-clustered indexes use the clustered indexes key as the pointer to the base row.
  • user_updates – The number of times the index has been modified due to a change in the table's data.
  • last_user_seek – The date and time of the last user seek operation
  • last_user_scan – The date and time of the last user scan operation
  • last_user_lookup – The date and time of the last user lookup operation
  • last_user_update – The date and time of the last user update operation
  • system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update - Same as the user query columns, but records when the index is used for a system operation, such as automatic statistics operations.

This is one of the most interesting views that I often use in performance tuning. It gives you something that no previous version of SQL Server did (at least in an easy to discover manner for the "average" dba, that I know of): The ability to tell when indexes are NOT being used. It is easy to see when an index is being used by a query by simply looking at the plan. But now, using this dynamic management view, you can see over time what indexes are used, not used, and probably more importantly, updated many many times and never being used.

Examples:

Create table, do a few queries in tempdb.  Table and full code will be in final book.

--returns all indexes for a database and their stats.
--Rows with no usage since the last restart will be null

select object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) as objectName,
         indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc,
         ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
from sys.indexes
               left outer join sys.dm_db_index_usage_stats ddius
                        on indexes.object_id = ddius.object_id
                             and indexes.index_id = ddius.index_id
                             and ddius.database_id = db_id()
order by ddius.user_seeks + ddius.user_scans + ddius.user_lookups desc

Published Sunday, July 22, 2007 10:45 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

One of my favorites. It's amazing how often you'll find production databases (even 3rd party systems) that have wasted indices.

I once looked at a system with this view that had over half of the indices rarely if every used. Just goes to show you that indexing doesn't work with a shotgun approach.

July 22, 2007 11:48 PM
 

drsql said:

James:  Yeah, I know.  Though in some ways you might expect it since they cannot predict perfectly what people might do with their stuff.  Hopefully in a version or two of SQL Server, this stuff can be done automatically with some precision.  This is an excellent first step, though.

July 27, 2007 9:38 PM
 

Ktk said:

Very helpfull to us newbies.  Thanks for making it so easy.

March 21, 2008 10:38 AM
 

Zack Jones said:

This is great information! Would you mind providing some additional information such as what values we should look at to determine if an index should be dropped or not. For example if user_updates is > 10 but user seeks is 0 then drop it, etc.

May 19, 2008 7:54 AM
 

Krishna said:

This is very very useful information.

I see the completeness to this article, if it also gives a benchmark values on "Index Usage Stats" to drop and re-factor index(s) on a Production DB.

Thanks, Krishna.

DB Architect. Sr. Lead DBA.

April 9, 2009 6:00 AM
 

Abe Miessler said:

Very good information.  Thanks for the explanation.

February 11, 2010 2:17 PM
 

Jackman said:

Hi

I have a Clustered Index which has user_lookup of 111449.  

It has the following stats

updates   Seeks    Scans     Lookups

5837 11674 0 111449

I always thought Lookups were associated with Non-CLustsred Indexes, what am I supposed to infer from this Data?

There are other Non-Clustsred Indexes which are using the Clustsred INdexes -- but still doing a lookup.  

Any ideas will be appreciated.

Thanks

January 24, 2011 7:45 AM
 

Nicole said:

So.. is it safe to say that there is no way to determine the last time a table was viewed/read withiout creating an external table that will record the last_read dates every time SQL is opened?

August 2, 2011 11:37 AM
 

Olu said:

Hi Louis,

Thanks for your explanation on what the columns really mean - I had a general idea but this was just great. It will realy help me to determine which indexes to keep or get rid off.

September 29, 2011 8:14 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement