Tracking index maintanence information
By: Chad Boyd | Updated: 2007-10-15 | Comments (2) | Related: More > Indexing
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.
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
Once the above function and procedure are created you can run this from any user database as follows. This will return information for all indexes or you can specify the following parameters:
- @tableName -- Name of a specific table/view/object to retrieve index information for - if null/default/0, -- no specific table filter is used
- @rowcount int -- Value to limit the result set to (top x) - if not passed, all data is returned
- @order nvarchar(100) -- sort to use for the batch request - can be a valid specification of columns in the query
USE databaseName -- change to your database name EXEC sp_indexOperationalInfo
Last Updated: 2007-10-15
About the author
View all my tips