Better Index Strategy With DMVs

One of the more important (and contentious) structures for database applications are indexes.  When used well they can aid an application greatly, but when used poorly they can cause greater overhead than if no index existed at all. 

There are two scenarios I will be discussing today that make use of the Dynamic Management View (DMV from here) sys.dm_db_index_usage_stats.  This DMV records counts of index operations by type.  It is important to note that the counters of this DMV are emptied with each restart of the SQL Server service.

The first scenario is a good introduction to this DMV if you are unfamiliar with it.  We simply want to find our most active indexes.  We can use the following query to return this detail:

--Get most used indexes Insert/Update/Delete and Selects
SELECT SCHEMA_NAME(o.[schema_id]) AS SchemaName
    ,o.name AS ObjectName
    ,i.name AS IndexName
    ,i.type_desc AS IndexType
    ,(COALESCE(us.user_seeks, 0) + 
        COALESCE(us.user_scans, 0) + 
        COALESCE(us.user_lookups, 0) + 
        COALESCE(us.user_updates, 0)
    ) AS Activity
FROM sys.objects AS o
    INNER JOIN sys.indexes AS i 
        ON i.[object_id] = o.[object_id]
    LEFT JOIN sys.dm_db_index_usage_stats AS us 
        ON us.[object_id] = o.[object_id]
        AND us.index_id = i.index_id
WHERE o.is_ms_shipped = 0
ORDER BY Activity DESC;

In sys.dm_db_index_usage_stats we get counters for each seek, scan, and lookup by a user query.  Each “update” query also gets a counter.  This can be misleading at first as you might think the name is exact to its usage.  This isn’t the case.  In actuality, INSERT/UPDATE/DELETE activities all get recorded under this column.  So in the above query we are able to find all activity against an index and rank them accordingly.  This can then be factored in to your overall maintenance strategy.

Next, we will look at a slightly different type of analysis.  One of the hidden costs of indexing is maintenance.  I’ve seen this blown off during modeling/development and assumed as a zero cost item.  Unfortunately, everything in life has a cost.  With data (and data access) growing, it’s vitally important that we don’t waste time on operations that add no value.  To that point, I will use the same DMV but this time finding indexes with a higher maintenance cost than usage.

--Indexes with more Insert/Update/Delete operations than Selects
;WITH IndexAnalysis AS
(
SELECT SCHEMA_NAME(o.[schema_id]) AS SchemaName
    ,o.name AS ObjectName
    ,i.name AS IndexName
    ,i.type_desc AS IndexType
    ,(COALESCE(us.user_seeks, 0) + 
        COALESCE(us.user_scans, 0) + 
        COALESCE(us.user_lookups, 0)
    ) AS QueryActivity
    ,COALESCE(us.user_updates, 0) AS MaintenanceActivity
FROM sys.objects AS o
    INNER JOIN sys.indexes AS i 
        ON i.[object_id] = o.[object_id]
    LEFT JOIN sys.dm_db_index_usage_stats AS us 
        ON us.[object_id] = o.[object_id]
        AND us.index_id = i.index_id
WHERE o.is_ms_shipped = 0
)
SELECT *
FROM IndexAnalysis
WHERE MaintenanceActivity > QueryActivity
ORDER BY MaintenanceActivity DESC;

By combining the query activities and then comparing against maintenance cost we can clearly state the “value” of an index.  From here it is simply a matter of pruning the indexes that have a greater overhead.  For large or older implementations this can usually net quite a boost in resource time as maintenance activities decrease.

I’m going to continue with the this general topic in following posts as there are many other DMVs that can help us make the most of our indexes.


Posted by: whitneyw
Posted on: 5/11/2009 at 12:58 PM
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed
Comments are closed