 |
|
Tracking index maintanence information
|
By: Chad Boyd | Read Comments | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More |
|
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
Last Update: 10/15/2007
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |