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()
| 
