DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_server  31 Aug 14Defects 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 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        declare @whoami varchar(30)
52        declare @rtnstatus int
53        declare @monprocname varchar(255)
54    
55        select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
56    
57        select @monprocname = 'sybsystemprocs.dbo.sp_monitor_check_permission'
58    
59        exec @rtnstatus = @monprocname @whoami
60    
61        if (@rtnstatus = 1)
62            return (1)
63    
64        /*
65        **  Set @mspertick.  This is just used to make the numbers easier to handle
66        **  and avoid overflow.
67        */
68    
69        select @mspertick = (@@timeticks / 1000.0)
70        /*
71        **  Set engonline to number of engines currently on line (so busy/idle
72        **  figures are correct). If this changes under us, the figures will not
73        **  necessarily be accurate for the next sp_monitor call; this should not
74        **  be a tremendous problem.
75        */
76    
77        select @engonline = count(*) from master.dbo.sysengines
78    
79    
80        /*
81        **  Get current monitor values.
82        */
83        select
84            @now = getdate(),
85            @cpu_busy = @@cpu_busy,
86            @io_busy = @@io_busy,
87            @idle = @@idle,
88            @pack_received = @@pack_received,
89            @pack_sent = @@pack_sent,
90            @connections = @@connections,
91            @pack_errors = @@packet_errors,
92            @total_read = @@total_read,
93            @total_write = @@total_write,
94            @total_errors = @@total_errors
95    
96        /*
97        **  Check to see if DataServer has been rebooted.  If it has then the
98        **  value of @@boottime will be more than the value of spt_monitor.lastrun.
99        **  If it has update spt_monitor.
100       */
101       update master.dbo.spt_monitor
102       set
103           lastrun = @now,
104           cpu_busy = @cpu_busy,
105           io_busy = @io_busy,
106           idle = @idle,
107           pack_received = @pack_received,
108           pack_sent = @pack_sent,
109           connections = @connections,
110           pack_errors = @pack_errors,
111           total_read = @total_read,
112           total_write = @total_write,
113           total_errors = @total_errors
114       where datediff(ss, lastrun, @@boottime) > 0
115   
116       /*
117       **  Now print out old and new monitor values.
118       */
119       set nocount on
120       select @interval = datediff(ss, lastrun, @now)
121       from master.dbo.spt_monitor
122       /* To prevent a divide by zero error when run for the first
123       ** time after boot up
124       */
125       if @interval = 0
126           select @interval = 1
127       select last_run = lastrun, current_run = @now, seconds = @interval
128       from master.dbo.spt_monitor
129       select
130           cpu_busy = convert(char(25),
131           convert(varchar(11),
132           convert(int,
133           (@cpu_busy * (@mspertick / 1000))))
134           + "("
135           + convert(varchar(11),
136           convert(int,
137           ((@cpu_busy - cpu_busy) * (@mspertick / 1000))
138           / @engonline))
139           + ")"
140           + "-"
141           + convert(varchar(11),
142           convert(int,
143           (((@cpu_busy - cpu_busy)
144           * (@mspertick / 1000))
145           / @engonline)) * 100
146           / @interval)
147           + "%"),
148   
149           io_busy = convert(char(25),
150           convert(varchar(11),
151           convert(int,
152           (@io_busy * (@mspertick / 1000))
153           / @engonline))
154           + "("
155           + convert(varchar(11),
156           convert(int,
157           ((@io_busy - io_busy) * (@mspertick / 1000))
158           / @engonline))
159           + ")"
160           + "-"
161           + convert(varchar(11),
162           convert(int,
163           (((@io_busy - io_busy) * (@mspertick / 1000))
164           / @engonline))
165           * 100
166           / @interval)
167           + "%"),
168           idle = convert(char(25),
169           convert(varchar(11),
170           convert(int,
171           (@idle * (@mspertick / 1000))
172           / @engonline))
173           + "("
174           + convert(varchar(11),
175           convert(int,
176           ((@idle - idle) * (@mspertick / 1000))
177           / @engonline))
178           + ")"
179           + "-"
180           + convert(varchar(11),
181           convert(int,
182           (((@idle - idle) * (@mspertick / 1000))
183           / @engonline))
184           * 100
185           / @interval)
186           + "%")
187   
188       from master.dbo.spt_monitor
189   
190       select
191           packets_received = convert(char(25), convert(varchar(11), @pack_received) + "(" +
192           convert(varchar(11), @pack_received - pack_received) + ")"),
193           packets_sent = convert(char(25), convert(varchar(11), @pack_sent) + "(" +
194           convert(varchar(11), @pack_sent - pack_sent) + ")"),
195           packet_errors = convert(char(25), convert(varchar(11), @pack_errors) + "(" +
196           convert(varchar(11), @pack_errors - pack_errors) + ")")
197       from master.dbo.spt_monitor
198   
199       select
200           total_read = convert(char(19), convert(varchar(11), @total_read) + "(" +
201           convert(varchar(11), @total_read - total_read) + ")"),
202           total_write = convert(char(19), convert(varchar(11), @total_write) + "(" +
203           convert(varchar(11), @total_write - total_write) + ")"),
204           total_errors = convert(char(19), convert(varchar(11), @total_errors) + "(" +
205           convert(varchar(11), @total_errors - total_errors) + ")"),
206           connections = convert(char(18), convert(varchar(11), @connections) + "(" +
207           convert(varchar(11), @connections - connections) + ")")
208       from master.dbo.spt_monitor
209   
210       /*
211       **  Now update spt_monitor
212       */
213       update master.dbo.spt_monitor
214       set
215           lastrun = @now,
216           cpu_busy = @cpu_busy,
217           io_busy = @io_busy,
218           idle = @idle,
219           pack_received = @pack_received,
220           pack_sent = @pack_sent,
221           connections = @connections,
222           pack_errors = @pack_errors,
223           total_read = @total_read,
224           total_write = @total_write,
225           total_errors = @total_errors
226   
227       return (0)
228   


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 213
 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 101
 MNER 3 No Error Check should check @@error after update 213
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 227
 MUOT 3 Updates outside transaction 213
 MZMB 3 Zombie: use of non-existent object sybsystemprocs.dbo.sp_monitor_check_permission 0
 VUNU 3 Variable is not used @rptline 49
 VUNU 3 Variable is not used @procval 50
 MDYE 2 Dynamic Exec Marker exec @rtnstatus 59
 MRST 2 Result Set Marker 127
 MRST 2 Result Set Marker 129
 MRST 2 Result Set Marker 190
 MRST 2 Result Set Marker 199
 MTR1 2 Metrics: Comments Ratio Comments: 23% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 2dec - 1exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 50 29

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..spt_monitor (1)  
reads table master..sysengines (1)