(─━┘_└━─)/ 머리만1톤(Head1Ton)

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