Analyze Index Statistics

Description

Sample stored procedure that analyzes index statistics, including accesses, overhead, locks, blocks, and waits. The order of execution is as follows: 1) Truncate indexstats with the script Create/Truncate an Indexstats Table; (2) Take initial index snapshot using the script Retrieve Index Statistics; (3) Run workload; (4) Take final index snapshot using the script Retrieve Index Statistics; and, 5) Use this script to perform the final analysis. See script comments for additional information. This stored procedure, contributed by Microsoft's Tom Davidson, requires SQL Server 2005.

Script Code

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[get_indexstats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[get_indexstats]
GO
create proc dbo.get_indexstats 
	(@dbid smallint=-1
	,@top varchar(100)=NULL
	,@columns varchar(500)=NULL
	,@order varchar(100)='lock waits'
	,@threshold varchar(500)=NULL)
as

-- @dbid limits analysis to a database
-- @top allows you to specify TOP n
-- @columns is used to specify what columns from 
--			sys.dm_db_index_operational_stats will be included in the report
--			For example, @columns='scans,lookups,waits' will include columns
--			containing these keywords
-- @order used to order results
-- @threshold used to add a threshold, 
--			example: @threshold='[block %] > 5' only include if blocking is over 5%
--
------  definition of some computed columns returned
-- [blk %] = percentage of locks that cause blocks e.g. blk% = 100 * lock waits / locks
-- [index usage] = range_scan_count + singleton_lookup_count + leaf_insert_count
-- [nonleaf index overhead]=nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count
-- [avg row lock wait ms]=row_lock_wait_in_ms/row_lock_wait_count
-- [avg page lock wait ms]=page_lock_wait_in_ms/page_lock_wait_count
-- [avg page latch wait ms]=page_latch_wait_in_ms/page_latch_wait_count
-- [avg pageio latch wait ms]=page_io_latch_wait_in_ms/page_io_latch_wait_count
---------------------------------------------------------------------------------------------------
--- Case 1 - only one snapshot of sys.dm_db_operational_index_stats was stored in 
---			indexstats.  This is an error - return errormsg to user
--- Case 2 - beginning snapshot taken, however some objects were not referenced
---			at the time of the beginning snapshot.  Thus, they will not be in the initial
---			snapshot of sys.dm_db_operational_index_stats, use 0 for starting values.
---			Print INFO msg for informational purposes.
--- Case 3 - beginning and ending snapshots, beginning values for all objects and indexes
---			this should be the normal case, especially if SQL Server is up a long time
---------------------------------------------------------------------------------------------------
set nocount on
declare @orderby varchar(100), @where_dbid_is varchar(100), @temp varchar(500), @threshold_temptab varchar(500)
declare @cmd varchar(max),@col_stmt varchar(500),@addcol varchar(500)
declare @begintime datetime, @endtime datetime, @duration datetime, @mincount int, @maxcount int

select @begintime = min(now), @endtime = max(now) from indexstats

if @begintime = @endtime
	begin
		print 'Error: indexstats contains only 1 snapshot of sys.dm_db_index_operational_stats'
		print 'Order of execution is as follows: '
		print '	(1) truncate indexstats with init_indexstats'
		print '	(2) take initial index snapshot using insert_indexstats'
		print '	(3) Run workload'
		print '	(4) take final index snapshot using insert_indexstats'
		print '	(5) analyze with get_indexstats'
		return -99
	end

select @mincount = count(*) from indexstats where now = @begintime
select @maxcount = count(*) from indexstats where now = @endtime

if @mincount < @maxcount
	begin
		print 'InfoMsg1: sys.dm_db_index_operational_stats only contains entries for objects referenced since last SQL re-cycle'
		print 'InfoMsg2: Any newly referenced objects and indexes captured in the ending snapshot will use 0 as a beginning value'
	end

select @top = case 
		when @top is NULL then ''
		else lower(@top)
	end,
		@where_dbid_is = case (@dbid)
		when -1 then ''
		else ' and i1.database_id = ' + cast(@dbid as varchar(10))
	end,
--- thresholding requires a temp table
		@threshold_temptab = case 
		when @threshold is NULL then ''
		else ' select * from #t where ' + @threshold
	end
--- thresholding requires temp table, add 'into #t' to select statement 
select @temp = case (@threshold_temptab)
		when '' then ''
		else ' into #t '
	end
select @orderby=case(@order)
when 'leaf inserts' then 'order by [' + @order + ']'
when 'leaf deletes' then 'order by [' + @order + ']'
when 'leaf updates' then 'order by [' + @order + ']'
when 'nonleaf inserts' then 'order by [' + @order + ']'
when 'nonleaf deletes' then 'order by [' + @order + ']'
when 'nonleaf updates' then 'order by [' + @order + ']'
when 'nonleaf index overhead' then 'order by [' + @order + ']' 
when 'leaf allocations' then 'order by [' + @order + ']'
when 'nonleaf allocations' then 'order by [' + @order + ']'
when 'allocations' then 'order by [' + @order + ']'
when 'leaf page merges' then 'order by [' + @order + ']'
when 'nonleaf page merges' then 'order by [' + @order + ']'
when 'range scans' then 'order by [' + @order + ']'
when 'singleton lookups' then 'order by [' + @order + ']'
when 'index usage' then 'order by [' + @order + ']'
when 'row locks' then 'order by [' + @order + ']'
when 'row lock waits' then 'order by [' + @order + ']'
when 'block %' then 'order by [' + @order + ']' 
when 'row lock wait ms' then 'order by [' + @order + ']'
when 'avg row lock wait ms' then 'order by [' + @order + ']'
when 'page locks' then 'order by [' + @order + ']'
when 'page lock waits' then 'order by [' + @order + ']'
when 'page lock wait ms' then 'order by [' + @order + ']'
when 'avg page lock wait ms' then 'order by [' + @order + ']'
when 'index lock promotion attempts' then 'order by [' + @order + ']'
when 'index lock promotions' then 'order by [' + @order + ']'
when 'page latch waits' then 'order by [' + @order + ']'
when 'page latch wait ms' then 'order by [' + @order + ']'
when 'pageio latch waits' then 'order by [' + @order + ']'
when 'pageio latch wait ms' then 'order by [' + @order + ']'
else ''
end

if @orderby <> '' select @orderby = @orderby + ' desc'
select 'start time'=@begintime,'end time'=@endtime,'duration (hh:mm:ss:ms)'=convert(varchar(50),@endtime-@begintime,14)
		,'Report'=case (@dbid) 
			when -1 then 'all databases'
			else db_name(@dbid)
		end
		+case 
		    when @top = '' then ''
			when @top is NULL then ''
			when @top = 'none' then ''
			else ', ' + @top
		end
	+case 
		    when @columns = '' then ''
			when @columns is NULL then ''
			when @columns = 'none' then ''
			else ', include only columns containing ' + @columns
		end
		+case(@orderby)
			when '' then ''
			when NULL then ''
			when 'none' then ''
			else ', ' + @orderby
		end
		+case
			when @threshold = '' then ''
			when @threshold is NULL then ''
			when @threshold = 'none' then ''
			else ', threshold on ' + @threshold
		end

select @cmd = ' select i2.database_id, i2.object_id, i2.index_id, i2.partition_number '
select @cmd = @cmd +' , begintime=case min(i1.now) when max(i2.now) then NULL else min(i1.now) end '
select @cmd = @cmd +' 	, endtime=max(i2.now) '
select @cmd = @cmd +' into #i '
select @cmd = @cmd +' from indexstats i2 '
select @cmd = @cmd +' full outer join '
select @cmd = @cmd +' 	indexstats i1 '
select @cmd = @cmd +' on i1.database_id = i2.database_id '
select @cmd = @cmd +' and i1.object_id = i2.object_id '
select @cmd = @cmd +' and i1.index_id = i2.index_id '
select @cmd = @cmd +' and i1.partition_number = i2.partition_number '
select @cmd = @cmd +' where i1.now >= ''' +  convert(varchar(100),@begintime, 109) + ''''
select @cmd = @cmd +' and i2.now = ''' + convert(varchar(100),@endtime, 109) + ''''
select @cmd = @cmd + ' ' + @where_dbid_is + ' '
select @cmd = @cmd + ' group by i2.database_id, i2.object_id, i2.index_id, i2.partition_number '
select @cmd = @cmd + ' select ' + @top + ' i.database_id, db_name=db_name(i.database_id), object=isnull(object_name(i.object_id),i.object_id), indid=i.index_id, part_no=i.partition_number '
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[leaf inserts]=i2.leaf_insert_count - isnull(i1.leaf_insert_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[leaf deletes]=i2.leaf_delete_count - isnull(i1.leaf_delete_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[leaf updates]=i2.leaf_update_count - isnull(i1.leaf_update_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[nonleaf inserts]=i2.nonleaf_insert_count - isnull(i1.nonleaf_insert_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[nonleaf deletes]=i2.nonleaf_delete_count - isnull(i1.nonleaf_delete_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[nonleaf updates]=i2.nonleaf_update_count - isnull(i1.nonleaf_update_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[nonleaf index overhead]=(i2.nonleaf_insert_count - isnull(i1.nonleaf_insert_count,0)) + (i2.nonleaf_delete_count - isnull(i1.nonleaf_delete_count,0)) + (i2.nonleaf_update_count - isnull(i1.nonleaf_update_count,0))'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[leaf allocations]=i2.leaf_allocation_count - isnull(i1.leaf_allocation_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[nonleaf allocations]=i2.nonleaf_allocation_count - isnull(i1.nonleaf_allocation_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[allocations]=(i2.leaf_allocation_count - isnull(i1.leaf_allocation_count,0)) + (i2.nonleaf_allocation_count - isnull(i1.nonleaf_allocation_count,0))'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[leaf page merges]=i2.leaf_page_merge_count - isnull(i1.leaf_page_merge_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[nonleaf page merges]=i2.nonleaf_page_merge_count - isnull(i1.nonleaf_page_merge_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[range scans]=i2.range_scan_count - isnull(i1.range_scan_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[singleton lookups]=i2.singleton_lookup_count - isnull(i1.singleton_lookup_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[index usage]=(i2.range_scan_count - isnull(i1.range_scan_count,0)) + (i2.singleton_lookup_count - isnull(i1.singleton_lookup_count,0)) + (i2.leaf_insert_count - isnull(i1.leaf_insert_count,0))'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[row locks]=i2.row_lock_count - isnull(i1.row_lock_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[row lock waits]=i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[block %]=cast (100.0 * (i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)) / (1 + i2.row_lock_count - isnull(i1.row_lock_count,0)) as numeric(5,2))'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[row lock wait ms]=i2.row_lock_wait_in_ms - isnull(i1.row_lock_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[avg row lock wait ms]=cast ((1.0*(i2.row_lock_wait_in_ms - isnull(i1.row_lock_wait_in_ms,0)))/(1 + i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[page locks]=i2.page_lock_count - isnull(i1.page_lock_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[page lock waits]=i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[page lock wait ms]=i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[avg page lock wait ms]=cast ((1.0*(i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)))/(1 + i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[index lock promotion attempts]=i2.index_lock_promotion_attempt_count - isnull(i1.index_lock_promotion_attempt_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[index lock promotions]=i2.index_lock_promotion_count - isnull(i1.index_lock_promotion_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[page latch waits]=i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[page latch wait ms]=i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[avg page latch wait ms]=cast ((1.0*(i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)))/(1 + i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[pageio latch waits]=i2.page_io_latch_wait_count - isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[pageio latch wait ms]=i2.page_io_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column @add_stmt=@addcol out,@cols_containing=@columns,@col_stmt=' ,[avg pageio latch wait ms]=cast ((1.0*(i2.page_io_latch_wait_in_ms - isnull(i1.page_io_latch_wait_in_ms,0)))/(1 + i2.page_io_latch_wait_count - isnull(i1.page_io_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
select @cmd = @cmd + @temp
select @cmd = @cmd + ' from #i i '
select @cmd = @cmd + ' left join indexstats i1 on i.begintime = i1.now and i.database_id = i1.database_id and i.object_id = i1.object_id and i.index_id = i1.index_id and i.partition_number = i1.partition_number '
select @cmd = @cmd + ' left join indexstats i2 on i.endtime = i2.now and i.database_id = i2.database_id and i.object_id = i2.object_id and i.index_id = i2.index_id and i.partition_number = i2.partition_number '
select @cmd = @cmd + ' ' + @orderby + ' '
select @cmd = @cmd + @threshold_temptab
--select @cmd
exec ( @cmd )
go
--- get all index column stats for dbid=7, order by singleton lookups desc
exec get_indexstats @dbid=7
				,@order='singleton lookups'
--- get the top 5 indexes for all databases, order by index usage desc
exec get_indexstats @dbid=-1,@top='top 5',@columns='index,usage',@order='index usage'
--- get the top 5 (all columns) index lock promotions where a lock promotion was attempted
exec get_indexstats @dbid=7,@top='top 5'
				,@order='index lock promotions',@threshold='[index lock promotion attempts] > 0'
--- get all index stats for top 10 nonleaf index overhead (cost of index)
exec get_indexstats @dbid=-1,@top='top 10',@order='nonleaf index overhead',@columns='leaf'
/*
--- get top 5 singleton lookups with avg row lock waits>2ms, return columns containing wait, scan, singleton
exec get_indexstats @dbid=5,@top='top 5',@columns='wait,scan,singleton'
				,@order='singleton lookups',@threshold='[avg row lock wait ms] > 2'
--- get all indexstats for dbid=5, order by index usage desc, threshold on block%, include columns containing usage,block,lock,wait
exec get_indexstats @dbid=-1,@columns='usage,block,lock,wait',
				@order='index usage',@threshold='[block %] > .005'
--- get indexstats for all dbid, include columns containing usage,wait
exec get_indexstats @dbid=-1,@columns='usage,wait',@order='row lock waits'
--- get top10, all index stats, order by row lock waits where block% > .01%
exec get_indexstats @dbid=-1,@top='top 10 ',@order='row lock waits',
				@threshold='[block %] > .01'
--- get top 10 for all databases, columns containing 'avg, wait', order by wait ms, where row lock waits > 1
exec get_indexstats @dbid=-1,@top='top 10 ',@columns='wait,row',
				@order='row lock wait ms', @threshold='[row lock waits] > 1'
--- get top 5 index stats, order by avg row lock waits desc
exec get_indexstats @dbid=-1,@top='top 5',@order='avg row lock wait ms'
--- get top 5 index stats, order by avg page latch lock waits desc
exec get_indexstats @dbid=-1,@top='top 5',@order='avg page latch wait ms'
--- get top 5 percent index stats, order by avg pageio latch waits desc where there were pageio latch waits
exec get_indexstats @dbid=-1,@top='top 3 percent',@order='avg pageio latch wait ms',
				@threshold='[pageio latch waits] > 0'
exec get_indexstats @dbid=-1,@top='top 10 percent',@order='page latch waits'
--- get all index stats for top 10 in db=5, ordered by block%
exec get_indexstats @dbid=5,@top='top 10',@order='block %'
--- get all index stats for top 10 in db=5, ordered by block% where block% > .1
exec get_indexstats @dbid=-1,@top='top 10',@order='block %',@threshold='[block %] > 0.1'
exec get_indexstats @dbid=-1,@top='top 5',@order='allocations',@columns='allocation'
*/
exec get_indexstats @dbid=-1,@top='top 10 ',@order='row lock waits'

				,@order='singleton lookups'rds

For online peer support, join the microsoft.public.windows.server.scripting community on the msnews.microsoft.com news server. To provide feedback or report bugs in sample scripts or the Scripting Guide, please contact Microsoft TechNet.

Disclaimer

The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

Top of page