How Useful Are Those Indexes

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.

sys.dm_db_index_usage_stats

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, let’s 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.

Conclusion

SQL Server 2005’s “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.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles