{"id":82010,"date":"2007-04-15T14:58:09","date_gmt":"2007-04-15T14:58:09","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73145"},"modified":"2018-12-12T13:43:25","modified_gmt":"2018-12-12T13:43:25","slug":"dynamic-management-function-query-to-view-index-usage","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/dynamic-management-function-query-to-view-index-usage\/","title":{"rendered":"Dynamic management function query to view index usage"},"content":{"rendered":"

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

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<\/a>, referring to this Microsoft SQL Server Development Customer Advisory Team blog entry on index evaluation<\/a> and a nice blog<\/a> on Denis’ sqlservercode.blogspot.com<\/a> blog too.) <\/p>\n

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.) <\/p>\n

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 <\/p>\n

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 <\/p>\n

<\/blockquote>\n

Here is a summary of what the different output columns mean:<\/p>\n