QiQ7vKdM

1// тестирование SQL сервера
2//
3// После создания вызываешь EXEC track_waitstats 300, 1
4//
5// 300 - минуты скока мерить
6// 1 - интервал в минутах
7
8 
9CREATE proc get_waitstats
10AS
11
12SET nocount ON
13
14DECLARE @now datetime,@totalwait numeric(20,1)
15   ,@endtime datetime,@begintime datetime
16   ,@hr int,@min int,@sec int
17
18SELECT  @now=max(now),@begintime=min(now),@endtime=max(now)
19FROM waitstats WHERE [wait type] = 'Total'
20
21-- Subtract waitfor, sleep, and resource_queue from total.
22SELECT @totalwait = sum([wait time]) + 1 FROM waitstats WHERE [wait type] NOT IN ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total',
23  '***total***') AND now = @now
24
25DELETE waitstats WHERE [wait type] = '***total***' AND now = @now INSERT INTO waitstats SELECT '***total***',0,@totalwait,@totalwait,@now
26
27SELECT [wait type],[wait time],percentage=cast (100*[wait time]/@totalwait
28  AS numeric(20,1))
29FROM waitstats
30WHERE [wait type] NOT IN ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
31AND now = @now
32ORDER BY percentage desc
33
34GO
35
36CREATE proc track_waitstats (@num_samples int=10,@delaynum int=1,@delaytype
37  nvarchar(10)='minutes')
38AS
39
40SET nocount ON
41IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'waitstats')
42   CREATE table waitstats ([wait type] varchar(80),
43      requests numeric(20,1),
44	  [wait time] numeric (20,1),
45	  [signal wait time] numeric(20,1),
46	  now datetime default getdate())
47ELSE    truncate table waitstats
48dbcc sqlperf (waitstats,clear)                            -- Clear out waitstats.
49DECLARE @i int,@delay varchar(8),@dt varchar(3),@now datetime,
50   @totalwait numeric(20,1),@endtime datetime,@begintime datetime,@hr int,
51   @min int,@sec int
52SELECT @i = 1
53SELECT @dt = case lower(@delaytype)
54   WHEN 'minutes' THEN 'm'
55   WHEN 'minute' THEN 'm'
56   WHEN 'min' THEN 'm'
57   WHEN 'mm' THEN 'm'
58   WHEN 'mi' THEN 'm'
59   WHEN 'm' THEN 'm'
60   WHEN 'seconds' THEN 's'
61   WHEN 'second' THEN 's'
62   WHEN 'sec' THEN 's'
63   WHEN 'ss' THEN 's'
64   WHEN 's' THEN 's'
65   ELSE @delaytype
66END
67IF @dt NOT IN ('s','m')
68BEGIN
69   PRINT 'please supply delay type e.g. seconds or minutes'
70   RETURN
71END
72IF @dt = 's'
73BEGIN
74   SELECT @sec = @delaynum % 60
75   SELECT @min = cast((@delaynum / 60) AS int)
76   SELECT @hr = cast((@min / 60) AS int)
77   SELECT @min = @min % 60
78END
79IF @dt = 'm'
80BEGIN
81   SELECT @sec = 0
82   SELECT @min = @delaynum % 60
83   SELECT @hr = cast((@delaynum / 60) AS int) END SELECT @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
84   + right('0'+convert(varchar(2),@min),2) + ':' +
85   + right('0'+convert(varchar(2),@sec),2)
86IF @hr > 23 or @min > 59 or @sec > 59
87BEGIN
88   SELECT 'hh:mm:ss delay time cannot > 23:59:59'
89   SELECT 'delay interval and type: ' + convert (varchar(10),@delaynum) + ',' + 
90     @delaytype + ' converts to ' + @delay
91   RETURN
92END
93WHILE (@i <= @num_samples)
94BEGIN
95             INSERT INTO waitstats ([wait type], requests, [wait time],[signal wait time])
96   EXEC ('dbcc sqlperf(waitstats)')
97   SELECT @i = @i + 1
98   waitfor delay @delay
99END
100-- Create report.
101EXECUTE get_waitstats
102
103GO
104SET QUOTED_IDENTIFIER OFF
105GO
106SET ANSI_NULLS ON
107GO