Tracking index maintanence information

use master
go
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
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 master.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

USE databaseName -- change to your database name
EXEC sp_indexOperationalInfo