Last week, I presented a query to view I/O pressure, and (to me) more usefully, look at it in a slice of time, like when doing a performance test. 

Here is a a very similar solution for index usage using the sys.dm_db_index_usage_stats dynamic management views. I will probably do several more of these types of blogs, as there is some really useful information in the dynamic management views, but the ones that start when the server is reset are troublesome when doing specific, time windowed performance tuning, something I am working on pretty heavy for the next week or so.  (I mentioned this dmv a while back in my blog here, referring to this Microsoft SQL Server Development Customer Advisory Team blog entry on index evaluation and a nice blog on Denis’ sqlservercode.blogspot.com blog too.)

This script will build a temp table of the results from sys.dm_db_index_usage_stats (user stats only), and the current time.  The @resetBaseLine variable is there to drop and recreate the #baseline table.  It works only in the current database (the sys.dm_db_index_usage_stats dmv works in any database, but sys.indexes will not.)

declare @resetbaseLine bit, @dbname sysname
set @resetbaseLine =1
declare @dbid int
select @dbid = db_id()
set nocount on
if @resetBaseLine = 1 or object_id(‘tempdb..#baseline’) is null
    begin
        if object_id(‘tempdb..#baseline’) is not null
              drop table #baseline
        select object_name(i.object_id) as object_name
                , i.object_id
                , i.name as index_name
                , case when i.is_unique = 1 then ‘UNIQUE ‘ else ” end + i.type_desc as index_type
                , i.index_id
                , coalesce(user_seeks,0) as user_seeks
                , coalesce(user_scans,0) as user_scans
                , coalesce(user_lookups,0) as user_lookups
                , coalesce(user_updates,0) as user_updates
                , getdate() as baselineDate
        into #baseline
        from sys.indexes i
                    left outer join sys.dm_db_index_usage_stats s
                        on i.object_id = s.object_id
                            and i.index_id = s.index_id
                            and database_id = @dbid 
        where objectproperty(i.object_id , ‘IsUserTable’) = 1
    end

select   #baseline.object_name, #baseline.index_name, #baseline.index_type
            , currentLine.user_seeks – #baseline.user_seeks as user_seeks
            , currentLine.user_scans – #baseline.user_scans as user_scans
            , currentLine.user_lookups – #baseline.user_lookups as user_lookups
            , currentLine.user_updates – #baseline.user_updates as user_updates
            , case when last_user_seek >= #baseline.baselineDate then last_user_seek else null end
                    as last_user_seek
            , case when last_user_scan >= #baseline.baselineDate then last_user_scan else null end
                    as last_user_scan
            , case when last_user_lookup >= #baseline.baselineDate then last_user_lookup else null end
                    as last_user_lookup
            , case when last_user_update >= #baseline.baselineDate then last_user_update else null end
                    as last_user_update
            , dateDiff(second,#baseline.baselineDate,getdate()) as seconds_since_baseline
            , dateDiff(minute,#baseline.baselineDate,getdate()) as minutes_since_baseline
from #baseline
                left join ( select s.object_id 
                                    , s.index_id
                                    , user_seeks
                                    , user_scans
                                    , user_lookups
                                    , user_updates
                                    , last_user_seek
                                    , last_user_scan
                                    , last_user_lookup
                                    , last_user_update
                            from sys.dm_db_index_usage_stats s
                            where database_id = @dbid
                               and objectproperty(s.object_id , ‘IsUserTable’) = 1 ) as currentLine
                    on #baseline.object_id = currentLine.object_id
                         and #baseline.index_id = currentLine.index_id
order by ( currentLine.user_seeks – #baseline.user_seeks +
                currentLine.user_scans – #baseline.user_scans +
                currentLine.user_lookups – #baseline.user_lookups + 
                currentLine.user_updates – #baseline.user_updates ) desc

Here is a summary of what the different output columns mean:

• object_name – the table or view that has the index
• index_name – the name of the index
• index_type – type of index, whether it is unique or not, and if it is clustered, heap, or nonclustered
• user_seeks – number of seeks for a particular key caused by user queries
• user_scans – number of scans through an index’s leaf nodes caused by user queries
• user_lookups – number of key lookups caused by user queries
• user_updates – number of modifications caused by user queries
• last_user_seek – last time the index was used in a seek
• last_user_scan – last time the index was scanned in a user query
  last_user_lookup – last time the clustered index was to lookup an individual row from a non-clustered index seek.  This information validated from Chris Hedgate’s blog entry: Index Lookups, Seeks, and Scans.)
• last_user_update – last time the index modified by a user query.

Crossposted to: http://drsql.spaces.live.com