DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_server  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Generated by spgenmsgs.pl on Tue Dec  2 17:55:42 2003
5     **
6     ** raiserror Messages for monitor_server [Total 0]
7     **
8     ** sp_getmessage Messages for monitor_server [Total 0]
9     **
10    ** End spgenmsgs.pl output.
11    **
12    */
13    
14    /*
15    ** This stored procedure is a subordinate stored procedure that is invoked from the
16    ** parent stored procedure sp_monitor when "server" is passed as the
17    ** argument or when no argument is passed. 
18    ** This stored procedure provides monitoring information pertaining
19    ** to the server as a whole.
20    **
21    ** Parameters:
22    **	None.
23    **
24    ** Returns:
25    **      0       - upon sucessful completion
26    **      1       - If an error resulted.
27    */
28    
29    create or replace procedure sp_monitor_server
30    as
31    
32        /*
33        **  Declare variables to be used to hold current monitor values.
34        */
35        declare @now datetime
36        declare @cpu_busy int
37        declare @io_busy int
38        declare @idle int
39        declare @pack_received int
40        declare @pack_sent int
41        declare @pack_errors int
42        declare @connections int
43        declare @total_read int
44        declare @total_write int
45        declare @total_errors int
46        declare @engonline int
47        declare @interval int
48        declare @mspertick float /* milliseconds per tick */
49        declare @rptline char(255)
50        declare @procval int
51    
52        /*
53        **  Set @mspertick.  This is just used to make the numbers easier to handle
54        **  and avoid overflow.
55        */
56    
57        select @mspertick = (@@timeticks / 1000.0)
58        /*
59        **  Set engonline to number of engines currently on line (so busy/idle
60        **  figures are correct). If this changes under us, the figures will not
61        **  necessarily be accurate for the next sp_monitor call; this should not
62        **  be a tremendous problem.
63        */
64    
65        select @engonline = count(*) from master.dbo.sysengines
66    
67    
68        /*
69        **  Get current monitor values.
70        */
71        select
72            @now = getdate(),
73            @cpu_busy = @@cpu_busy,
74            @io_busy = @@io_busy,
75            @idle = @@idle,
76            @pack_received = @@pack_received,
77            @pack_sent = @@pack_sent,
78            @connections = @@connections,
79            @pack_errors = @@packet_errors,
80            @total_read = @@total_read,
81            @total_write = @@total_write,
82            @total_errors = @@total_errors
83    
84        /*
85        **  Check to see if DataServer has been rebooted.  If it has then the
86        **  value of @@boottime will be more than the value of spt_monitor.lastrun.
87        **  If it has update spt_monitor.
88        */
89        update master.dbo.spt_monitor
90        set
91            lastrun = @now,
92            cpu_busy = @cpu_busy,
93            io_busy = @io_busy,
94            idle = @idle,
95            pack_received = @pack_received,
96            pack_sent = @pack_sent,
97            connections = @connections,
98            pack_errors = @pack_errors,
99            total_read = @total_read,
100           total_write = @total_write,
101           total_errors = @total_errors
102       where datediff(ss, lastrun, @@boottime) > 0
103   
104       /*
105       **  Now print out old and new monitor values.
106       */
107       set nocount on
108       select @interval = datediff(ss, lastrun, @now)
109       from master.dbo.spt_monitor
110       /* To prevent a divide by zero error when run for the first
111       ** time after boot up
112       */
113       if @interval = 0
114           select @interval = 1
115       select last_run = lastrun, current_run = @now, seconds = @interval
116       from master.dbo.spt_monitor
117       select
118           cpu_busy = convert(char(25),
119           convert(varchar(11),
120           convert(int,
121           (@cpu_busy * (@mspertick / 1000))))
122           + "("
123           + convert(varchar(11),
124           convert(int,
125           ((@cpu_busy - cpu_busy) * (@mspertick / 1000))
126           / @engonline))
127           + ")"
128           + "-"
129           + convert(varchar(11),
130           convert(int,
131           (((@cpu_busy - cpu_busy)
132           * (@mspertick / 1000))
133           / @engonline)) * 100
134           / @interval)
135           + "%"),
136   
137           io_busy = convert(char(25),
138           convert(varchar(11),
139           convert(int,
140           (@io_busy * (@mspertick / 1000))
141           / @engonline))
142           + "("
143           + convert(varchar(11),
144           convert(int,
145           ((@io_busy - io_busy) * (@mspertick / 1000))
146           / @engonline))
147           + ")"
148           + "-"
149           + convert(varchar(11),
150           convert(int,
151           (((@io_busy - io_busy) * (@mspertick / 1000))
152           / @engonline))
153           * 100
154           / @interval)
155           + "%"),
156           idle = convert(char(25),
157           convert(varchar(11),
158           convert(int,
159           (@idle * (@mspertick / 1000))
160           / @engonline))
161           + "("
162           + convert(varchar(11),
163           convert(int,
164           ((@idle - idle) * (@mspertick / 1000))
165           / @engonline))
166           + ")"
167           + "-"
168           + convert(varchar(11),
169           convert(int,
170           (((@idle - idle) * (@mspertick / 1000))
171           / @engonline))
172           * 100
173           / @interval)
174           + "%")
175   
176       from master.dbo.spt_monitor
177   
178       select
179           packets_received = convert(char(25), convert(varchar(11), @pack_received) + "(" +
180           convert(varchar(11), @pack_received - pack_received) + ")"),
181           packets_sent = convert(char(25), convert(varchar(11), @pack_sent) + "(" +
182           convert(varchar(11), @pack_sent - pack_sent) + ")"),
183           packet_errors = convert(char(25), convert(varchar(11), @pack_errors) + "(" +
184           convert(varchar(11), @pack_errors - pack_errors) + ")")
185       from master.dbo.spt_monitor
186   
187       select
188           total_read = convert(char(19), convert(varchar(11), @total_read) + "(" +
189           convert(varchar(11), @total_read - total_read) + ")"),
190           total_write = convert(char(19), convert(varchar(11), @total_write) + "(" +
191           convert(varchar(11), @total_write - total_write) + ")"),
192           total_errors = convert(char(19), convert(varchar(11), @total_errors) + "(" +
193           convert(varchar(11), @total_errors - total_errors) + ")"),
194           connections = convert(char(18), convert(varchar(11), @connections) + "(" +
195           convert(varchar(11), @connections - connections) + ")")
196       from master.dbo.spt_monitor
197   
198       /*
199       **  Now update spt_monitor
200       */
201       update master.dbo.spt_monitor
202       set
203           lastrun = @now,
204           cpu_busy = @cpu_busy,
205           io_busy = @io_busy,
206           idle = @idle,
207           pack_received = @pack_received,
208           pack_sent = @pack_sent,
209           connections = @connections,
210           pack_errors = @pack_errors,
211           total_read = @total_read,
212           total_write = @total_write,
213           total_errors = @total_errors
214   
215       return (0)
216   


exec sp_procxmode 'sp_monitor_server', 'AnyMode'
go

Grant Execute on sp_monitor_server to public
go
RESULT SETS
sp_monitor_server_rset_004
sp_monitor_server_rset_003
sp_monitor_server_rset_002
sp_monitor_server_rset_001

DEFECTS
 QUDW 4 Update or delete with no where clause 201
 TNOI 4 Table with no index master..spt_monitor master..spt_monitor
 TNOI 4 Table with no index master..sysengines master..sysengines
 MGTP 3 Grant to public master..spt_monitor  
 MGTP 3 Grant to public master..sysengines  
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_server  
 MLCH 3 Char type with length>30 char(255) 49
 MNER 3 No Error Check should check @@error after update 89
 MNER 3 No Error Check should check @@error after update 201
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 215
 MUOT 3 Updates outside transaction 201
 VUNU 3 Variable is not used @rptline 49
 VUNU 3 Variable is not used @procval 50
 MRST 2 Result Set Marker 115
 MRST 2 Result Set Marker 117
 MRST 2 Result Set Marker 178
 MRST 2 Result Set Marker 187
 MTR1 2 Metrics: Comments Ratio Comments: 24% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 2dec - 1exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 41 29

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_monitor_server_rset_001 
writes table sybsystemprocs..sp_monitor_server_rset_003 
writes table sybsystemprocs..sp_monitor_server_rset_002 
reads table master..sysengines (1)  
writes table sybsystemprocs..sp_monitor_server_rset_004 
read_writes table master..spt_monitor (1)