Share via

What indexes are used most often (or least often) on my server?

  • Article

A customer this past week was curious how to determine which indexes on their server were being used either very frequently or very infrequently. This type of information can be invaluable when tuning a server on both ends: finding the most frequently used structures can aid in tracking down hotspots in your system for update-based operations and also provide some guidance on where to target your performance tuning efforts; finding the least frequently used structures can help with finding unnecessary indexes or indexes which are used sometimes but the maintenance overhead associated with keeping them updated outweighs their benefit.

With Sql 2000, determining this type of information was extremely difficult to say the least - it would have required capturing an extensive server trace and applying some very intense analytics to the captured data. With Sql 2005, this type of information is infinitely easier to find using the DMV's provided with the engine. I'm not going to go into a deep discussion on the DMV's themselves, as they've been talked about quite extensively elsewhere - I will however provide you some initial scripts that gather this type of information in a variety of ways.

The new sys.dm_db_index_usage_stats DMV provides you with information on usage characteristics for indexes within your system including the number of user-based and system-based seeks, scans, lookups, and update operations executed against each index. User based counters are incremented due to user operations (like running a query) and system based counters are incremented due to system level operations (like stat updates). Seeks, scan, and lookups occur to satisfy read-based operations, whereas updates occur to satisfy write-based operations (insert/update/deletes).

So, with this information, what types of things should I look for?  Good question, here's some thoughts:

    -  Sorting on user seeks, scans, and/or lookups (or a combination thereof) will provide you a list of the most heavily used indexes to satisfy user queries - this shows you generally the 'hottest' objects in your system (i.e. those that are queried most frequently).
   
    -  Sorting on user lookups will provide you a list of the cluster/heap structures that are used most frequently to satisfy bookmark-lookup type operations for additional data from a non-clustered index seek/scan. Combined with correlating this information against non-clustered index structures for the same object that have high user seek values can give you some insight into the non-clustered indexes that are probably used most frequently to satisfy user requests without enough data to 'cover' the request.
   
    -  Sorting on update operations will give you a list of indexes that are most frequently updated by user/system operations (insert/update/delete) - correlating high update operations with low seek/scan/lookup operations will give you clues as to indexes that are probably costing more to maintain than are useful for improved read-speed.
   
The information contained in the DMV is not persisted across service starts/stops, so if you want to keep information from this (and other) DMV's, be sure to persist it elsewhere by capturing the data and storing in a table of some sort.

Ok, so how can you query this data?  Well, here's a sample stored procedure that I use for querying against it...in addition to the information from the DMV itself, this procedure gives you the ability to sort in multiple ways, size information for each index (row counts, storage space), and column lists for each index (using a helper function also included). I'll let you play around with it if you like, or by all means just use it as a guide to come up with something of your own that suites you or your organization better...enjoy...

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

 

----------------------------------------------------------------------
------------------ CODE ONLY BELOW ------------------
----------------------------------------------------------------------

if object_id('dbo.fn_indexColumnList') > 0
 drop function dbo.fn_indexColumnList
go

create function dbo.fn_indexColumnList(@objectId int, @indexId int)
returns nvarchar(max)
as
/*
Returns a text-based list of column names, in key order, for the object/index combination passed
*/
begin
 declare @colList nvarchar(max);
 set @colList = N'';

 -- First, get just the key columns...
 select @colList = @colList + case when len(@colList) > 0 then ',' else '' end + c.name
 from sys.index_columns ic with(nolock)
 join sys.columns c
 on  ic.object_id = c.object_id
 and  ic.column_id = c.column_id
 where  ic.object_id = @objectId
 and  ic.index_id = @indexId
 and  ic.key_ordinal > 0
 and  ic.is_included_column = 0
 order by ic.key_ordinal;
 
 -- Now append any included columns...
 if exists(select * from sys.index_columns where object_id = @objectId and index_id = @indexId and is_included_column > 0) begin
  set @colList = @colList + ' (';
  
  select @colList = @colList + c.name + ','
  from sys.index_columns ic with(nolock)
  join sys.columns c
  on  ic.object_id = c.object_id
  and  ic.column_id = c.column_id
  where  ic.object_id = @objectId
  and  ic.index_id = @indexId
  and  ic.is_included_column > 0;
  
  set @colList = @colList + '$$^^$$';
  set @colList = replace(@colList,',$$^^$$',')');
 end

return @colList;
end

use master
go

if ((object_id('sp_indexUsageInfo') is not null) and (objectproperty(object_id('sp_indexUsageInfo'), 'IsProcedure') = 1))
 drop proc [dbo].sp_indexUsageInfo
go

create proc [dbo].sp_indexUsageInfo
 @tableName nvarchar(255) = null, -- Name of a specific table/view/object to retrieve index usage information for - if null/default/0, no specific table filter is used
 @rowcount int = null,    -- Value to limit the result set to (top x) - if not passed, all data is returned
 @opts  int = 0     -- Bit flags that indicate what to return for the procedure call:
          -- 1 bit - if set, order of results will be descending instead of ascending (by default, we order showing least used first up to most used,
          --   if this bit is set, we do the opposite instead...
          -- 2 bit - if set, whatever sort order is used will be applied to system values instead of user values
as

/*

NOTE: Use of this procedure requires the existence of the following procedures/functions as well:
 1.  dbo.fn_indexColumnList()

-- Show all structures for the current db, in order of least user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, default
-- Only top 25
exec dbo.sp_indexUsageInfo default, 25, default

-- Show all structures for the current db, in order of most user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 1

-- Show all structures for the current db, in order of least system-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 2

-- Show all structures for the current db, in order of most system-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 3

-- Show all structures for the current db, for a single tabled called 'tblBatches', in order of least user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo 'tblBatches', default, default

-- Show all structures for the current db, for a single tabled called 'tblBatches', in order of most user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo 'tblBatches', default, 1
*/

set nocount on;
set transaction isolation level read uncommitted;

declare @sql   nvarchar(max),
  @databaseId  int,
  @order   nvarchar(1000);

-- Format incoming data
select @opts = case when @opts > 0 then @opts else 0 end,
  @sql = N'',
  @databaseId = db_id(),
  @rowcount = case when @rowcount > 0 then @rowcount else 0 end;

select @order = case
     when @opts & 3 = 3 then '(u.system_seeks + u.system_scans + u.system_lookups) desc, sizeData.rowCnt desc'
     when @opts & 3 = 2 then '(u.system_seeks + u.system_scans + u.system_lookups), sizeData.rowCnt desc'
     when @opts & 3 = 1 then '(u.user_seeks + u.user_scans + u.user_lookups) desc, sizeData.rowCnt desc'
     when @opts & 3 = 0 then '(u.user_seeks + u.user_scans + u.user_lookups), sizeData.rowCnt desc'
    end;
    
select @sql = @sql + N'
 select ' + case when @rowcount > 0 then ' top (@rowcount) ' else '' end +
 '  object_name(i.object_id) as objectName, isnull(i.name,''HEAP'') as indexName, i.type_desc as indexType,
   case when i.type_desc = ''HEAP'' then ''HEAP'' else dbo.fn_indexColumnList(i.object_id, i.index_id) end as columnList,
   sizeData.rowCnt as rowCnt, sizeData.totalSpaceMB as totalSpaceMB, sizeData.usedSpaceMB as usedSpaceMB,
   u.user_seeks as userSeeks, u.user_scans as userScans, u.user_lookups as userLookups, u.user_updates as userUpdates,
   u.system_seeks as sysSeeks, u.system_scans as sysScans, u.system_lookups as sysLookups, u.system_updates as sysUpdates,
   datediff(minute,u.last_user_seek,getdate()) as minutesSinceLastUserSeek, datediff(minute,u.last_user_scan,getdate()) as minutesSinceLastUserScan,
   datediff(minute,u.last_user_lookup,getdate()) as minutesSinceLastUserLookup, datediff(minute,u.last_user_update,getdate()) as minutesSinceLastUserUpdate,
   datediff(minute,u.last_system_seek,getdate()) as minutesSinceLastSystemSeek, datediff(minute,u.last_system_scan,getdate()) as minutesSinceLastSystemScan,
   datediff(minute,u.last_system_lookup,getdate()) as minutesSinceLastSystemLookup, datediff(minute,u.last_system_update,getdate()) as minutesSinceLastSystemUpdate
 from sys.indexes i
 join (
    select i.object_id as objectId, i.index_id as indexId,
      (sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
      (select sum(p2.rows) from sys.partitions p2 with(nolock) where p2.object_id = i.object_id and p2.index_id = i.index_id) as rowCnt
    from sys.indexes i
    join sys.partitions p
    on  i.object_id = p.object_id
    and  i.index_id = p.index_id
    join sys.allocation_units a
    on  p.partition_id = a.container_id ' +
    case when len(@tableName) > 0 then ' where i.object_id = object_id(@tableName) ' else '' end +
    'group by i.object_id, i.index_id, i.name
   ) sizeData
 on  i.object_id = sizeData.objectId
 and  i.index_id = sizeData.indexId
 left join sys.dm_db_index_usage_stats u
 on  u.object_id = i.object_id
 and  u.index_id = i.index_id
 and  u.database_id = @databaseId ' +
 case when len(@tableName) > 0 then ' where i.object_id = object_id(@tableName) ' else '' end +
 'order by ' + @order;

exec sp_executesql @sql, N'@tableName nvarchar(255), @databaseId int, @rowcount int', @tableName, @databaseId, @rowcount;
go

Comments