CREATE proc track_waitstats & CREATE proc get_waitstats - useful code for diagnosing SQL 2000 performance
For those attending my TechNet SQL 2000 performance tuning session at TVP Reading on Tuesday 25th Jan, here is the code for the track_waitstats and get_waitstats stored procedures I refer to during the presentation.
CREATE proc track_waitstats (@num_samples int=10,@delaynum int=1,@delaytype nvarchar(10)='minutes')
as
-- T. Davidson
-- 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 https://www.microsoft.com/info/cpyright.htm
-- @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. delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
set nocount on
if not exists (select 1 from sysobjects where name = 'waitstats')
create table waitstats ([wait type] varchar(80),
requests numeric(20,1),
[wait time] numeric (20,1),
[signal wait time] numeric(20,1),
now datetime default getdate())
else truncate table waitstats
dbcc sqlperf (waitstats,clear) -- clear out waitstats
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(@delaytype)
when 'minutes' then 'm'
when 'minute' then 'm'
when 'min' then 'm'
when 'mm' then 'm'
when 'mi' then 'm'
when 'm' then 'm'
when 'seconds' then 's'
when 'second' then 's'
when 'sec' then 's'
when 'ss' then 's'
when 's' then 's'
else @delaytype
end
if @dt not in ('s','m')
begin
print 'please supply delay type e.g. seconds or minutes'
return
end
if @dt = 's'
begin
select @sec = @delaynum % 60
select @min = cast((@delaynum / 60) as int)
select @hr = cast((@min / 60) as int)
select @min = @min % 60
end
if @dt = 'm'
begin
select @sec = 0
select @min = @delaynum % 60
select @hr = cast((@delaynum / 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 'hh:mm:ss delay time cannot > 23:59:59'
select 'delay interval and type: ' + convert (varchar(10),@delaynum) + ',' + @delaytype + ' converts to ' + @delay
return
end
while (@i <= @num_samples)
begin
insert into waitstats ([wait type], requests, [wait time],[signal wait time])
exec ('dbcc sqlperf(waitstats)')
select @i = @i + 1
waitfor delay @delay
End
--- create waitstats report
execute get_waitstats
CREATE proc get_waitstats
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 https://www.microsoft.com/info/cpyright.htm
--
-- this proc will create waitstats report listing wait types by percentage
-- can be run when track_waitstats is executing
set nocount on
declare @now datetime, @totalwait numeric(20,1)
,@endtime datetime,@begintime datetime
,@hr int, @min int, @sec int
select @now=max(now),@begintime=min(now),@endtime=max(now)
from waitstats where [wait type] = 'Total'
--- subtract waitfor, sleep, and resource_queue from Total
select @totalwait = sum([wait time]) + 1 from waitstats
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***') and now = @now
-- insert adjusted totals, rank by percentage descending
delete waitstats where [wait type] = '***total***' and now = @now
insert into waitstats select '***total***',0,@totalwait,@totalwait,@now
select [wait type],[wait time],percentage=cast (100*[wait time]/@totalwait as numeric(20,1))
from waitstats
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
and now = @now
order by percentage desc