Having indexes on your tables provides a method to improve performance of your T-SQL queries. However, indexes also increase the amount of work SQL Server needs to perform when inserting, updating, or deleting rows. As time passes, index performance can slow down as indexes become fragmented. As a DBA, you need to understand how your applications use the available indexes so you can make sure the indexes are performing optimally and are not causing excessive work for SQL Server. In this article, I will discuss one Dynamic Management View (DMV) that you can obtain information from that will help you understand how your indexes are being used.
The sys.dm_db_index_usage_stats DMV, as the name suggests, is a view that allows you to obtain index usage information. This DMV returns the number of user and system seeks, lookups, scans and updates performed, plus the last time each index was used since SQL Server was last started. In order to use this view you must have VIEW SERVER STATE permissions.
Each time an index is used SQL Server increments the appropriate counter columns in the sys.dm_db_index_usage_stats DMV depending on what kind of index operation is performed. The index count information is an accumulated count based on usage 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).
This view is useful to analyze how your indexes are being used. The information returned from this view can be used to determine the usefulness of your indexes, or whether you should consider performing routine maintenance on your indexes.
Using Information in sys.dm_db_index_usage_stats
You can use the information returned by the sys.dm_db_index_usage _stats view for a number of different purposes. I will review a few different ways you can use this view to understand how your indexes are being used.
First, lets say you want to identify which indexes that are not being used by your application. Being able to do this allows you to determine which indexes you might consider dropping. There is no sense in maintaining indexes that your application is not using.
The sys.dm_db_index_usage view tracks information about indexes that have been used, so you can use the information in this view to identify those indexes in your database that have not been used. To accomplish this you will need to write some code that would join the information from this view with your database sys.indexes table, and then return all the indexes that were not found in this DMV. Here is my SELECT statement that will display all the CLUSTERED and NON-CLUSTERED indexes in the AdventureWorks database that have not been used:
USE AdventureWorks go SELECT OBJECT_NAME(I.object_id) Object_Name ,I.name Index_Name ,CASE WHEN I.type = 1 THEN 'Clustered' WHEN I.type = 2 THEN 'Non-Clustered' ELSE 'Unknown' END Index_Type FROM sys.dm_db_index_usage_stats S RIGHT OUTER JOIN AdventureWorks.sys.indexes I ON S.index_id= I.index_id and S.object_id = I.object_Id and s.database_id = DB_ID('AdventureWorks') WHERE S.object_id is null and I.type in (1,2)
In this code, I used a RIGHT OUTER JOIN to join all the indexes in the AdventureWorks.sys.indexes table with the sys.dm_db_index_usage_stats view. I returned a row for each CLUSTERED (I.type = 1) or NON-CLUSTERED (I.type=2) index where the index was not found (S.object_id is null) in the sys.dm_db_index_usage view.
Keep in mind this DMV only tracks indexes that have been used since SQL Server has started. If you just recycled your SQL Server 2005 machine then this particular query might return all the indexes in your database. Therefore is it is best to run this query after your server has been up for a while and you have run a comprehensive set of queries against your databases.
Another use of the sys.dm_db_index_usage_stats DMV is to determine which indexes are the most useful to your application, and those that are seldom used. Along with the usage information you can also use this view to determine the different types of index operation (SCAN, SEEK, & LOOKUP) SQL Server has performed against each index. Here is some T-SQL code that will produce a report of all the CLUSTERED and NON-CLUSTERED indexes in the AdventureWorks database that SQL Server has performed either a SCAN, SEEK, or LOOKUP operation against:
use AdventureWorks go SELECT OBJECT_NAME(S.object_id) Object_Name ,I.name Index_Name ,CASE WHEN I.type = 1 THEN 'Clustered' WHEN I.type = 2 THEN 'Non-Clustered' ELSE 'Unknown' END Index_Type ,S.user_seeks ,S.user_scans ,S.user_lookups FROM sys.dm_db_index_usage_stats S JOIN sys.indexes I ON S.index_id= I.index_id and S.object_id = I.object_Id and s.database_id = DB_ID('AdventureWorks') join AdventureWorks.sys.objects O on O.Object_id = I.Object_ID WHERE (S.user_seeks > 0 or S.user_scans > 0 or S.user_lookups > 0) and I.type in (1,2)
After running this code, you can review the output to determine which indexes are being used the most or the least. The number of times these indexes are used will show up in the different user_... columns. By looking at these user . Columns you can assess which indexes are used frequently and those that are seldom used.
Indexes help SQL Server quickly resolve your T-SQL query to determine the specific rows you want to return. However, they also incur overhead when inserting, updating, and deleting rows. These insert, updates, and deletes can lead to index fragmentation. To help identify those indexes that could potentially be fragmented you can use this SELECT statement:
use AdventureWorks go SELECT OBJECT_NAME(S.object_id) Object_Name ,I.name Index_Name ,CASE WHEN I.type = 1 THEN 'Clustered' ELSE 'Non-Clustered' END ,S.user_seeks ,S.last_user_seek FROM sys.dm_db_index_usage_stats S LEFT JOIN sys.indexes I ON S.index_id= I.index_id and S.object_id = I.object_Id WHERE s.database_id = DB_ID() AND I.type in (1,2) AND S.user_updates > 0 AND S.user_seeks = 0 AND S.user_scans = 0 AND S.user_lookups = 0
This T-SQL returns the CLUSTERED and NON-CLUSTERED indexes that have been updated but never used in a LOOKUP, SEEK, or SCAN index operation. For each index returned from this command, you might consider performing some routine index maintenance to reduce fragmentation. Another option would be to remove the index if it is not being used. By removing the index, you would reduce the additional overhead in maintaining an index that is never used.
SQL Server 2005s sys.dm_db_index_usage_stats DMV provides a wealth of information regarding your database index usages. By analyzing the user updates, seeks, scans and lookup counts in this view you can determine how useful your indexes are in resolving your application queries. This article gives you some code samples that will help you review how well your indexes are doing their job at minimizing the work SQL Server 2005 performs when executing your T-SQL.