Mat Stephen's SQL Server WebLog

All things SQL Server. Complied by Matthew Stephen - SQL Server Product Specialist, Microsoft UK

Blogs

CREATE proc track_waitstats & CREATE proc get_waitstats - useful code for diagnosing SQL 2000 performance

  • Comments 3
  • Likes

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