컴퓨토피아

create proc dbo.sp_block (@spid bigint=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 reports blocks
-- 1. optional parameter @spid
--

select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)

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

create proc insert_indexstats (@dbid smallint=NULL,
@objid int=NULL,
@indid int=NULL,
@partitionid int=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
-- This stored procedure stores a snapshot of
sys.dm_db_index_operational_stats into the table indexstas
-- for later analysis by the stored procedure get_indexstats. Please note
that the indexstats table has an additional
-- column to store the timestamp when the snapshot is taken
--
-- T. Davidson
-- snapshot sys.dm_db_index_operational_stats
--
declare @now datetime
select @now = getdate()
insert into indexstats
(database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms,
now)
select database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms
,@now
from sys.dm_db_index_operational_stats(@dbid,@objid,@indid,@partitionid)
go

CREATE proc dbo.init_index_operational_stats
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
--
-- create indexstats table if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 from dbo.sysobjects where
id=object_id(N'[dbo].[indexstats]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
create table dbo.indexstats (
database_id smallint NOT NULL
,object_id int NOT NULL
,index_id int NOT NULL
,partition_number int NOT NULL
,leaf_insert_count bigint NOT NULL
,leaf_delete_count bigint NOT NULL
,leaf_update_count bigint NOT NULL
,leaf_ghost_count bigint NOT NULL
,nonleaf_insert_count bigint NOT NULL
,nonleaf_delete_count bigint NOT NULL
,nonleaf_update_count bigint NOT NULL
,leaf_allocation_count bigint NOT NULL
,nonleaf_allocation_count bigint NOT NULL
,leaf_page_merge_count bigint NOT NULL
,nonleaf_page_merge_count bigint NOT NULL
,range_scan_count bigint NOT NULL
,singleton_lookup_count bigint NOT NULL
,forwarded_fetch_count bigint NOT NULL
,lob_fetch_in_pages bigint NOT NULL
,lob_fetch_in_bytes bigint NOT NULL
,lob_orphan_create_count bigint NOT NULL
,lob_orphan_insert_count bigint NOT NULL
,row_overflow_fetch_in_pages bigint NOT NULL
,row_overflow_fetch_in_bytes bigint NOT NULL
,column_value_push_off_row_count bigint NOT NULL
,column_value_pull_in_row_count bigint NOT NULL
,row_lock_count bigint NOT NULL
,row_lock_wait_count bigint NOT NULL
,row_lock_wait_in_ms bigint NOT NULL
,page_lock_count bigint NOT NULL
,page_lock_wait_count bigint NOT NULL
,page_lock_wait_in_ms bigint NOT NULL
,index_lock_promotion_attempt_count bigint NOT NULL
,index_lock_promotion_count bigint NOT NULL
,page_latch_wait_count bigint NOT NULL
,page_latch_wait_in_ms bigint NOT NULL
,page_io_latch_wait_count bigint NOT NULL
,page_io_latch_wait_in_ms bigint NOT NULL
,now datetime default getdate())
else truncate table dbo.indexstats
go

create proc dbo.add_column (
@add_stmt varchar(500) output,
@find varchar(100)=NULL,
@cols_containing varchar(500)=NULL,
@col_stmt varchar(max))
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
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find.
-- If @col_stmt contains @find, include this statement.
-- set @add_stmt = @col_stmt
--
declare @length int, @strindex int, @EOS bit
if @cols_containing is NULL
begin
select @add_stmt=@col_stmt
return
end
select @add_stmt = '', @EOS = 0

while @add_stmt is not null and @EOS = 0
@dbid=-1,
select @strindex = charindex(',',@cols_containing)
if @strindex = 0
select @find = @cols_containing, @EOS = 1
else
begin
select @find = substring(@cols_containing,1,@strindex-1)
select @cols_containing =
substring(@cols_containing,
@strindex+1,
datalength(@cols_containing) - @strindex)
end
select @add_stmt=case
--when @cols_containing is NULL then NULL
when charindex(@find,@col_stmt) > 0 then NULL
else ''
end
end
--- NULL indicates this statement is to be passed back through out parm
@add_stmt
if @add_stmt is NULL select @add_stmt=@col_stmt
go

CREATE proc [dbo].[track_waitstats_2005] (
@num_samples int=10,
@delay_interval int=1,
@delay_type nvarchar(10)='minutes',
@truncate_history nvarchar(1)='N',
@clear_waitstats nvarchar(1)='Y')
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
-- @num_samples is the number of times to capture waitstats, default is 10
-- times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
-- Revision: 4/19/05
--- (1) added object owner qualifier
--- (2) optional parameters to truncate history and clear waitstats
set nocount on
if not exists (select 1
from sys.objects
where object_id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
create table [dbo].[waitstats]
([wait_type] nvarchar(60) not null,
[waiting_tasks_count] bigint not null,
[wait_time_ms] bigint not null,
[max_wait_time_ms] bigint not null,
[signal_wait_time_ms] bigint not null,
now datetime not null default getdate())

If lower(@truncate_history) not in (N'y',N'n')
begin
raiserror ('valid @truncate_history values are ''y'' or
''n''',16,1) with nowait
end
If lower(@clear_waitstats) not in (N'y',N'n')
begin
raiserror ('valid @clear_waitstats values are ''y'' or
''n''',16,1) with nowait
end
If lower(@truncate_history) = N'y'
truncate table dbo.waitstats

If lower (@clear_waitstats) = N'y'
-- clear out waitstats
dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs

declare @i int,
@delay varchar(8),
@dt varchar(3),
@now datetime,
@totalwait numeric(20,1),
@endtime datetime,
@begintime datetime,
@hr int,
@min int,
@sec int

select @i = 1
select @dt = case lower(@delay_type)
when N'minutes' then 'm'
when N'minute' then 'm'
when N'min' then 'm'
when N'mi' then 'm'
when N'n' then 'm'
when N'm' then 'm'
when N'seconds' then 's'
when N'second' then 's'
when N'sec' then 's'
when N'ss' then 's'
when N's' then 's'
else @delay_type
end

if @dt not in ('s','m')
begin
raiserror ('delay type must be either ''seconds'' or
''minutes''',16,1) with nowait
return
end
if @dt = 's'
begin
select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60)
as int), @hr = cast((@min / 60) as int)
end
if @dt = 'm'
begin
select @sec = 0, @min = @delay_interval % 60, @hr =
cast((@delay_interval / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
+ right('0'+convert(varchar(2),@min),2) + ':' +
+ right('0'+convert(varchar(2),@sec),2)

if @hr > 23 or @min > 59 or @sec > 59
begin
select 'delay interval and type: ' + convert
(varchar(10),@delay_interval) + ',' + @delay_type + ' converts to ' +
@delay
raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait
return
end
while (@i <= @num_samples)
begin
select @now = getdate()
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
@now
from sys.dm_os_wait_stats

insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
'Total',
sum([waiting_tasks_count]),
sum([wait_time_ms]),
0,
sum([signal_wait_time_ms]),
@now
from [dbo].[waitstats]
where now = @now

select @i = @i + 1
waitfor delay @delay
end
--- create waitstats report
execute dbo.get_waitstats_2005
go
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'

CREATE proc [dbo].[track_waitstats_2005] (
@num_samples int=10,
@delay_interval int=1,
@delay_type nvarchar(10)='minutes',
@truncate_history nvarchar(1)='N',
@clear_waitstats nvarchar(1)='Y')
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
-- @num_samples is the number of times to capture waitstats, default is 10
-- times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
-- Revision: 4/19/05
--- (1) added object owner qualifier
--- (2) optional parameters to truncate history and clear waitstats
set nocount on
if not exists (select 1
from sys.objects
where object_id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
create table [dbo].[waitstats]
([wait_type] nvarchar(60) not null,
[waiting_tasks_count] bigint not null,
[wait_time_ms] bigint not null,
[max_wait_time_ms] bigint not null,
[signal_wait_time_ms] bigint not null,
now datetime not null default getdate())

If lower(@truncate_history) not in (N'y',N'n')
begin
raiserror ('valid @truncate_history values are ''y'' or
''n''',16,1) with nowait
end
If lower(@clear_waitstats) not in (N'y',N'n')
begin
raiserror ('valid @clear_waitstats values are ''y'' or
''n''',16,1) with nowait
end
If lower(@truncate_history) = N'y'
truncate table dbo.waitstats

If lower (@clear_waitstats) = N'y'
-- clear out waitstats
dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs

declare @i int,
@delay varchar(8),
@dt varchar(3),
@now datetime,
@totalwait numeric(20,1),
@endtime datetime,
@begintime datetime,
@hr int,
@min int,
@sec int

select @i = 1
select @dt = case lower(@delay_type)
when N'minutes' then 'm'
when N'minute' then 'm'
when N'min' then 'm'
when N'mi' then 'm'
when N'n' then 'm'
when N'm' then 'm'
when N'seconds' then 's'
when N'second' then 's'
when N'sec' then 's'
when N'ss' then 's'
when N's' then 's'
else @delay_type
end

if @dt not in ('s','m')
begin
raiserror ('delay type must be either ''seconds'' or
''minutes''',16,1) with nowait
return
end
if @dt = 's'
begin
select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60)
as int), @hr = cast((@min / 60) as int)
end
if @dt = 'm'
begin
select @sec = 0, @min = @delay_interval % 60, @hr =
cast((@delay_interval / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
+ right('0'+convert(varchar(2),@min),2) + ':' +
+ right('0'+convert(varchar(2),@sec),2)

if @hr > 23 or @min > 59 or @sec > 59
begin
select 'delay interval and type: ' + convert
(varchar(10),@delay_interval) + ',' + @delay_type + ' converts to ' +
@delay
raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait
return
end
while (@i <= @num_samples)
begin
select @now = getdate()
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
@now
from sys.dm_os_wait_stats

insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
'Total',
sum([waiting_tasks_count]),
sum([wait_time_ms]),
0,
sum([signal_wait_time_ms]),
@now
from [dbo].[waitstats]
where now = @now

select @i = @i + 1
waitfor delay @delay
end
--- create waitstats report
execute dbo.get_waitstats_2005
go
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'

CREATE proc [dbo].[get_waitstats_2005] (
@report_format varchar(20)='all',
@report_order varchar(20)='resource')
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
--
-- this proc will create waitstats report listing wait types by
-- percentage.
-- (1) total wait time is the sum of resource & signal waits,
-- @report_format='all' reports resource & signal
-- (2) Basics of execution model (simplified)
-- a. spid is running then needs unavailable resource, moves to
-- resource wait list at time T0
-- b. a signal indicates resource available, spid moves to
-- runnable queue at time T1
-- c. spid awaits running status until T2 as cpu works its way
-- through runnable queue in order of arrival
-- (3) resource wait time is the actual time waiting for the
-- resource to be available, T1-T0
-- (4) signal wait time is the time it takes from the point the
-- resource is available (T1)
-- to the point in which the process is running again at T2.
-- Thus, signal waits are T2-T1
-- (5) Key questions: Are Resource and Signal time significant?
-- a. Highest waits indicate the bottleneck you need to solve
-- for scalability
-- b. Generally if you have LOW% SIGNAL WAITS, the CPU is
-- handling the workload e.g. spids spend move through
-- runnable queue quickly
-- c. HIGH % SIGNAL WAITS indicates CPU can't keep up,
-- significant time for spids to move up the runnable queue
-- to reach running status
-- (6) This proc can be run when track_waitstats is executing
--
-- Revision 4/19/2005
-- (1) add computation for CPU Resource Waits = Sum(signal waits /
-- total waits)
-- (2) add @report_order parm to allow sorting by resource, signal
-- or total waits
--
set nocount on

declare @now datetime,
@totalwait numeric(20,1),
@totalsignalwait numeric(20,1),
@totalresourcewait numeric(20,1),
@endtime datetime,@begintime datetime,
@hr int,
@min int,
@sec int

if not exists (select 1
from sysobjects
where id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
raiserror('Error [dbo].[waitstats] table does not exist',
16, 1) with nowait
return
end

if lower(@report_format) not in ('all','detail','simple')
begin
raiserror ('@report_format must be either ''all'',
''detail'', or ''simple''',16,1) with nowait
return
end
if lower(@report_order) not in ('resource','signal','total')
begin
raiserror ('@report_order must be either ''resource'',
''signal'', or ''total''',16,1) with nowait
return
end
if lower(@report_format) = 'simple' and lower(@report_order) <> 'total'
begin
raiserror ('@report_format is simple so order defaults to
''total''',
16,1) with nowait
select @report_order = 'total'
end


select
@now=max(now),
@begintime=min(now),
@endtime=max(now)
from [dbo].[waitstats]
where [wait_type] = 'Total'

--- subtract waitfor, sleep, and resource_queue from Total
select @totalwait = sum([wait_time_ms]) + 1, @totalsignalwait =
sum([signal_wait_time_ms]) + 1
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total' ,'WAITFOR',
'***total***') and
now = @now

select @totalresourcewait = 1 + @totalwait - @totalsignalwait

-- insert adjusted totals, rank by percentage descending
delete waitstats
where [wait_type] = '***total***' and
now = @now

insert into waitstats
select
'***total***',
0,@totalwait,
0,
@totalsignalwait,
@now

select 'start time'=@begintime,'end time'=@endtime,
'duration (hh:mm:ss:ms)'=convert(varchar(50),@endtime-
@begintime,14),
'report format'=@report_format, 'report order'=@report_order

if lower(@report_format) in ('all','detail')
begin
----- format=detail, column order is resource, signal, total. order by
resource desc
if lower(@report_order) = 'resource'
select [wait_type],[waiting_tasks_count],
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as
numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as
numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'res_wt_%' desc

----- format=detail, column order signal, resource, total. order by signal
desc
if lower(@report_order) = 'signal'
select [wait_type],
[waiting_tasks_count],
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait
as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as
numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as
numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'sig_wt_%' desc

----- format=detail, column order total, resource, signal. order by total
desc
if lower(@report_order) = 'total'
select
[wait_type],
[waiting_tasks_count],
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as
numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'wt_%' desc
end
else
---- simple format, total waits only
select
[wait_type],
[wait_time_ms],
percentage=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by percentage desc

---- compute cpu resource waits
select
'total waits'=[wait_time_ms],
'total signal=CPU waits'=[signal_wait_time_ms],
'CPU resource waits % = signal waits / total waits'=
cast (100*[signal_wait_time_ms]/[wait_time_ms] as
numeric(20,1)),
now
from [dbo].[waitstats]
where [wait_type] = '***total***'
order by now
go


declare @now datetime
select @now = getdate()
select getdate()