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 http://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 http://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


 


 

Comments (0)