SQL Server 2005
에서 대기(Wait)를 점검하는데 사용할 수 있는 저장 프로시저 스크립트입니다.
참고하세요.
사용하시기 전에 주석 내용 꼭 읽어 보시구요.
IF EXISTS (SELECT * from sys.objects where OBJECT_ID = OBJECT_ID(N'[dbo].[track_waitstats_2005]') and OBJECTPROPERTY(OBJECT_ID, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[track_waitstats_2005]
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'
DBCC sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs -- 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(@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
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
EXEC dbo.track_waitstats_2005 @num_samples=20
,@delay_interval=30
,@delay_type='s'
,@TRUNCATE_history='y'
,@clear_waitstats='y'
GO
