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
--
-- This stored procedure is provided "AS IS" with no warranties, and 
-- confers no rights. 
-- Use of included script samples are subject to the terms specified at 
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- This proc analyzes index statistics including accesses, overhead, 
-- locks, blocks, and waits
--
-- Instructions: Order of execution is as follows:
--	(1) truncate indexstats with init_indexstats
--	(2) take initial index snapshot using insert_indexstats
--	(3) Run workload
--	(4) take final index snapshot using insert_indexstats
--	(5) analyze with get_indexstats	

-- @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
exec ( @cmd )
go
