Similar to my post regarding index usage (most/least often), you can use the following code below to get index operational information (exposed via the sys.dm_db_index_operational_stats dynamic management function in Sql 2005).  You can include table filters, specify ordering operations, and specify specific row numbers...correlate this information with other data from the usage data, missing index information, etc., and you've got quite a bit of insight into what parts of your system are working efficiently, those that aren't, those that can be improved, possibly removed, etc., etc.

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 http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.

----------------------------------------------------------------------
------------------ 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_indexOperationalInfo') is not null) and (objectproperty(object_id('sp_indexOperationalInfo'), 'IsProcedure') = 1))
 drop proc [dbo].sp_indexOperationalInfo
go

create proc [dbo].sp_indexOperationalInfo
 @tableName nvarchar(255) = null, -- Name of a specific table/view/object to retrieve index 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
 @order  nvarchar(100) = null -- sort to use for the batch request - can be a valid specification of columns in the query
        
as

/*

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

exec dbo.sp_indexOperationalInfo '', 10, 's.row_lock_count'

*/

set nocount on;
set transaction isolation level read uncommitted;

declare @sql   nvarchar(max),
  @databaseId  int,
  @objectId  int;

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

if (charindex(' ', replace(@order,' desc','')) + charindex(char(9), @order) + charindex(char(13), @order) + charindex(char(10), @order) + charindex('-', @order) + charindex('/', @order)) > 0 begin
 raiserror ('ta ta ta...you can only use valid column names for ordering the list...', 17, 1);
 return;
end

select @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,
      partition_number as partitionNumber, leaf_insert_count as leafInsertCount, leaf_delete_count as leafDeleteCount,
      leaf_update_count as leafUpdateCount, nonleaf_insert_count as nonleafInsertCount, nonleaf_delete_count as nonleafDeleteCount,
      nonleaf_update_count as nonleafUpdateCount, leaf_allocation_count as leafAllocCount, nonleaf_allocation_count as nonleafAllocCount,
      leaf_page_merge_count as leafPageMergeCount, nonleaf_page_merge_count as nonleafPageMergeCount, range_scan_count as rangeScanCount,
      singleton_lookup_count as singletonLookupCount, lob_fetch_in_pages as lobFetchPages, lob_fetch_in_bytes as lobFetchBytes,
      lob_orphan_create_count as lobOrphanCreateCount, lob_orphan_insert_count as lobOrphanInsertCount,
      row_overflow_fetch_in_pages as rowOverflowFetchPages, row_overflow_fetch_in_bytes as rowOverflowFetchBytes,
      row_lock_count as rowLockCount, row_lock_wait_count as rowLockWaitCount, row_lock_wait_in_ms as rowLockWaitMS,
      page_lock_count as pageLockCount, page_lock_wait_count as pageLockWaitCount, page_lock_wait_in_ms as pageLockWaitMS,
      index_lock_promotion_attempt_count as indexLockPromotionAttemptCount, index_lock_promotion_count as indexLockPromotionCount,
      page_latch_wait_count as pageLatchWaitCount, page_latch_wait_in_ms as pageLatchWaitMS, page_io_latch_wait_count as pageIoLatchWaitCount,
      page_io_latch_wait_in_ms as pageIoLatchWaitMS
    from sys.indexes i
    join (
       select i.object_id as objectId, i.index_id as indexId,
         (sum(a.total_pages) * 8) / 1024 as totalSpaceMB,
         (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 = @objectId ' 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_operational_stats(@databaseId,' + case when len(@tableName) > 0 then '@objectId,' else 'default,' end + 'default,default) s
    on  i.object_id = s.object_id
    and  i.index_id = s.index_id
    and  s.database_id = @databaseId ' +
    case when len(@tableName) > 0 then ' where i.object_id = @objectId ' else '' end +
    case when len(@order) > 0 then 'order by ' + @order else '' end;
   

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