Obtaining Index Usage Information

When building an application there are a number of things that are important.  The first is having a good database design; a database structure that is easy to use and query.  Performance is another, a measure of performance it is how quickly your applications are able to retrieve and update required data.  A key component of SQL Server that allows queries to return data quickly is the indexes placed on tables.  Using good indexes that are appropriate for the application, enables the database engine to minimize the amount of work needed to return data.  Adversely if the database has to many indexes, updates and inserts; it may perform poorly while SQL Server spends CPU cycles and I/O’s to update all the necessary indexes.   This article will discuss how to identify which indexes are being used and which are not, in addition to how the 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).  These new DMVs and DMFs allow access to a wealth of information into how the SQL Server engine is performing and using resources.  One DMV that provides some great index usage statistics is sys.dm_db_index_usage_stats.

As the name suggests, this view allows access to statistics about how indexes are used, the DMV keeps statistics for indexes in all databases.  DMV reports have a number of counters for each index used to identify how it was used and dates that identify when an index was last used.  Index usage statistics can be used to determine how useful an index is.  Using the information returned from this view it is possible to determine if an index is improving or hindering application performance. 

In order to use this DMV, VIEW SERVER STATE permissions are needed.  If a user is unable to run the queries in the article due to permission problems please request them from the local DBA. If permission is unable to be permanently granted then request that the DBA run the queries on the behalf of the user.

How does tSQL Server gather statistics for this DMV
Every time a T-SQL statement is executed, SQL Server tracks the time and how an index is used.  For each statement executing, SQL Server, using the query plan information, increments 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 index 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).  The usage counters are not the number of I/O or PAGE read, but the number of times the index was used.   When looking at index usage statistics using the sys.dm_db_index_usage_stats DMV this must be kept in mind.  The longer SQL Server has been collection statistics, the more likely the statistics will be an accurate representation of how applications are taking advantage of the indexes.

Indentifying Indexes that are not being used
Having indexes in a database that are not being used by an application is akin to having an eight bedroom house, but only using three. Having eight bedrooms might be nice if you can afford them, but are also paying and maintaining five bedrooms that are not needed.    This additional maintenance cost is money that is effectively thrown away each year.  Having extra indexes on tables that are not being used still require SQL Server to maintain them every time an update occurs, and consume additional space if never used.  So like the extra bedrooms, these extra indexes are costing slower response times for updates and longer backup times for unused index storage.  So how to identify those indexes that are not used?

Below 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 index has been used.  This information is 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 it 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 it is possible to to capture all the indexes that have been used on a daily bases and store them in a file.  After a few months the information collected can be analysed to determine which indexes have not been used.  Below 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 run on an instance of SQL Server this is an expample of the output:



Examining this output it is possible to see how each index has been used by looking at the “user_…” columns.  The “user_seeks” column identifies the number of times an index seek operation has been used to traverse this index to resolve a T-SQL statement.  The “user_scan” column identifies the number time an index scan operation has been used.  The “user_lookups” column identifies the number of times the index has been used in an index lookup operation.  The “user_update” column to identifies how many times this index has been updated due to the table in which the index is associated has been updated by an UPDATE statement.  

Other Factors to Consider
The output of this view assists in determining how valuable indexes have been in resolving queries.  Using this information decisions can be made regarding the need to modify, and/or drop some indexes.  Remember that the information in the “sys.dm_db_index_usage_stats” index only contains statistics that have been gathered since SQL Server was started, the database was opened, or when the index was created.  If the system hasn’t been up very long then potentially the stats that this DMV produces might not be very useful in representing a true picture of the index utilization.  Ensure statistics are representative of the sample of queries executed to determine what maintenance activities are required based on the output of this DMV. 

Tuning Indexes
It is important for a DBA to make sure the indexes on a database are useful.  By using the output from the “sys.dm_db_index_usage_stats” DMV a good picture of how indexes are used develops.  This information can then be utilised to better tune indexes and maximize the performance of the database by removing indexes that are producing more overhead than performance gain.




Related Articles :

  • No Related Articles Found

2 Responses to “Obtaining Index Usage Information”

  1. I can’t see why
    “– Indexes that have been updated by not used”
    is
    “AND(s.user_seeks > 0 or s.user_scans > 0 or s.user_lookups > 0 );

    it should be “AND(s.user_seeks = 0 and s.user_scans = 0 an s.user_lookups = 0 and s.user_updates > 0 );

    right?

  2. DBA100,
    I used you and statement and found it quite useful for I was trying to find the indexes that were not being used. The fact that they are updated but never user seeked, scanned, or looked up is something I needed.

    So thanks for the additional code.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |