DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_netio  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** network activity.
4     */
5     create or replace procedure sp_sysmon_netio
6         @NumEngines tinyint, /* number of engines online */
7         @NumElapsedMs int, /* for "per Elapsed second" calculations */
8         @NumXacts int /* for per transactions calculations */
9     as
10    
11        /* --------- declare local variables --------- */
12        declare @i smallint /* loop index to iterate through multi-group 
13        ** counters (engine, disk, & buffer) */
14        declare @tmp_grp varchar(25) /* temp var for build group_names 
15        ** ie. engine_N, disk_N */
16        declare @tmp_int int /* temp var for integer storage */
17        declare @tmp_int2 int /* temp var for integer storage */
18        declare @tmp_total int /* temp var for summing 'total #s' data */
19        declare @sum1line char(80) /* string to delimit total lines without 
20        ** percent calc on printout */
21        declare @sum2line char(80) /* string to delimit total lines without 
22        ** percent calc on printout */
23        declare @subsection char(80) /* delimit disk sections */
24        declare @blankline char(1) /* to print blank line */
25        declare @psign char(3) /* hold a percent sign (%) for print out */
26        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
27        declare @rptline char(80) /* formatted stats line for print statement */
28        declare @section char(80) /* string to delimit sections on printout */
29    
30        /* --------- Setup Environment --------- */
31        set nocount on /* disable row counts being sent to client */
32    
33        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
34        select @sum2line = "  -------------------------  ------------  ------------  ----------"
35        select @subsection = "  -----------------------------------------------------------------------------"
36        select @blankline = " "
37        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
38        select @na_str = "n/a"
39        select @section = "==============================================================================="
40    
41        print @section
42        print @blankline
43        print "Network I/O Management"
44        print "----------------------"
45        print @blankline
46    
47        select @tmp_total = value
48        from #tempmonitors
49        where group_name = "kernel" and
50            field_name = "ksalloc_calls"
51    
52        print "  Network I/O Requests            per sec      per xact       count  %% of total"
53        print @sum1line
54    
55        select @rptline = "  Total Network I/O Requests " +
56            str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
57            space(2) +
58            str(@tmp_total / convert(real, @NumXacts), 12, 1) +
59            space(2) +
60            str(@tmp_total, 10) + space(7) +
61            @na_str
62        print @rptline
63    
64        if @tmp_total != 0
65        begin
66            select @tmp_int = value
67            from #tempmonitors
68            where group_name = "kernel" and
69                field_name = "ksalloc_sleeps"
70    
71            select @rptline = "  Network I/Os Delayed" + space(7) +
72                str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
73                space(2) +
74                str(@tmp_int / convert(real, @NumXacts), 12, 1) +
75                space(2) +
76                str(@tmp_int, 10) + space(5) +
77                str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
78            print @rptline
79        end
80        print @blankline
81        print @blankline
82    
83        /****************************************************
84        **						
85        **	Process mode version of report is per engine
86        **
87        *****************************************************/
88    
89        if @@kernelmode = "proces"
90        begin
91    
92            print "  Total TDS Packets Received      per sec      per xact       count  %% of total"
93            print @sum1line
94    
95            select @tmp_total = SUM(value)
96            from #tempmonitors
97            where group_name like "engine_%" and
98                field_name = "no_packets_received"
99    
100           if @tmp_total = 0
101           begin
102               select @rptline = "  Total TDS Packets Rec'd             0.0           0.0           0       n/a"
103               print @rptline
104           end
105           else
106           begin
107               select @i = 0
108               while @i < @NumEngines /* for each engine */
109               begin
110                   /* build group_name string */
111                   select @tmp_grp = "engine_" + convert(varchar(4), @i)
112   
113                   /* If an engine's clock_ticks counter is 0, this means this engine
114                   ** is in offline status. We should skip this engine when priting
115                   ** the statistic information.
116                   */
117                   if (select value
118                           from #tempmonitors where field_name = "clock_ticks"
119                               and group_name = @tmp_grp) > 0
120                   begin
121                       select @tmp_int = value
122                       from #tempmonitors
123                       where group_name = @tmp_grp and
124                           field_name = "no_packets_received"
125   
126                       select @rptline = "    Engine " + convert(char(4), @i) +
127                           space(14) +
128                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
129                           space(2) +
130                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
131                           space(2) +
132                           str(@tmp_int, 10) + space(5) +
133                           str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
134                       print @rptline
135                   end
136   
137                   select @i = @i + 1
138               end /* while loop */
139   
140               print @sum2line
141   
142               select @rptline = "  Total TDS Packets Rec'd" + space(4) +
143                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
144                   space(2) +
145                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
146                   space(2) +
147                   str(@tmp_total, 10)
148               print @rptline
149           end
150           print @blankline
151           print @blankline
152   
153           /* 
154           ** save total packets rec'd for avg bytes / pkt calc 
155           */
156           select @tmp_int2 = @tmp_total
157   
158           print "  Total Bytes Received            per sec      per xact       count  %% of total"
159           print @sum1line
160   
161           select @tmp_total = SUM(value)
162           from #tempmonitors
163           where group_name like "engine_%" and
164               field_name = "no_bytes_received"
165   
166           if @tmp_total = 0
167           begin
168               select @rptline = "  Total Bytes Rec'd                   0.0           0.0           0       n/a"
169               print @rptline
170           end
171           else
172           begin
173               select @i = 0
174               while @i < @NumEngines /* for each engine */
175               begin
176                   /* build group_name string */
177                   select @tmp_grp = "engine_" + convert(varchar(4), @i)
178   
179                   /* If an engine's clock_ticks counter is 0, this means this engine
180                   ** is in offline status. We should skip this engine when priting
181                   ** the statistic information.
182                   */
183                   if (select value
184                           from #tempmonitors where field_name = "clock_ticks"
185                               and group_name = @tmp_grp) > 0
186                   begin
187                       select @tmp_int = value
188                       from #tempmonitors
189                       where group_name = @tmp_grp and
190                           field_name = "no_bytes_received"
191   
192                       select @rptline = "    Engine " + convert(char(4), @i) +
193                           space(14) +
194                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
195                           space(2) +
196                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
197                           space(2) +
198                           str(@tmp_int, 10) + space(5) +
199                           str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
200                       print @rptline
201                   end
202   
203                   select @i = @i + 1
204               end /* while loop */
205   
206               print @sum2line
207   
208               select @rptline = "  Total Bytes Rec'd" + space(10) +
209                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
210                   space(2) +
211                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
212                   space(2) +
213                   str(@tmp_total, 10)
214               print @rptline
215           end /* else */
216           print @blankline
217           print @blankline
218   
219           if @tmp_int2 != 0 /* Avoid divide by zero. */
220           begin
221               select @rptline = "   Avg Bytes Rec'd per Packet" + space(10) +
222                   @na_str + space(11) +
223                   @na_str + space(2) +
224                   str(@tmp_total / @tmp_int2, 10) + space(7) +
225                   @na_str
226               print @rptline
227               print @blankline
228           end
229           print @subsection
230           print @blankline
231   
232           print "  Total TDS Packets Sent          per sec      per xact       count  %% of total"
233           print @sum1line
234   
235           select @tmp_total = SUM(value)
236           from #tempmonitors
237           where group_name like "engine_%" and
238               field_name = "no_packets_sent"
239   
240           if @tmp_total = 0
241           begin
242               select @rptline = "  Total TDS Packets Sent              0.0           0.0           0       n/a"
243               print @rptline
244           end
245           else
246           begin
247               select @i = 0
248               while @i < @NumEngines /* for each engine */
249               begin
250                   /* build group_name string */
251                   select @tmp_grp = "engine_" + convert(varchar(4), @i)
252   
253                   /* If an engine's clock_ticks counter is 0, this means this engine
254                   ** is in offline status. We should skip this engine when priting
255                   ** the statistic information.
256                   */
257                   if (select value
258                           from #tempmonitors where field_name = "clock_ticks"
259                               and group_name = @tmp_grp) > 0
260                   begin
261                       select @tmp_int = value
262                       from #tempmonitors
263                       where group_name = @tmp_grp and
264                           field_name = "no_packets_sent"
265   
266                       select @rptline = "    Engine " + convert(char(4), @i) +
267                           space(14) +
268                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
269                           space(2) +
270                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
271                           space(2) +
272                           str(@tmp_int, 10) + space(5) +
273                           str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
274                       print @rptline
275                   end
276   
277                   select @i = @i + 1
278               end /* while loop */
279   
280               print @sum2line
281   
282               select @rptline = "  Total TDS Packets Sent" + space(5) +
283                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
284                   space(2) +
285                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
286                   space(2) +
287                   str(@tmp_total, 10)
288               print @rptline
289           end /* else */
290           print @blankline
291           print @blankline
292   
293           /* save total packets sent for avg bytes / pkt calc */
294           select @tmp_int2 = @tmp_total
295   
296           print "  Total Bytes Sent                per sec      per xact       count  %% of total"
297           print @sum1line
298   
299           select @tmp_total = SUM(value)
300           from #tempmonitors
301           where group_name like "engine_%" and
302               field_name = "no_bytes_sent"
303   
304           if @tmp_total = 0
305           begin
306               select @rptline = "  Total Bytes Sent                    0.0           0.0           0       n/a"
307               print @rptline
308           end
309           else
310           begin
311               select @i = 0
312               while @i < @NumEngines /* for each engine */
313               begin
314                   /* build group_name string */
315                   select @tmp_grp = "engine_" + convert(varchar(4), @i)
316   
317                   /* If an engine's clock_ticks counter is 0, this means this engine
318                   ** is in offline status. We should skip this engine when priting
319                   ** the statistic information.
320                   */
321                   if (select value
322                           from #tempmonitors where field_name = "clock_ticks"
323                               and group_name = @tmp_grp) > 0
324                   begin
325                       select @tmp_int = value
326                       from #tempmonitors
327                       where group_name = @tmp_grp and
328                           field_name = "no_bytes_sent"
329   
330                       select @rptline = "    Engine " + convert(char(4), @i) +
331                           space(14) +
332                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
333                           space(2) +
334                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
335                           space(2) +
336                           str(@tmp_int, 10) + space(5) +
337                           str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
338                       print @rptline
339                   end
340   
341                   select @i = @i + 1
342               end /* while loop */
343   
344               print @sum2line
345   
346               select @rptline = "  Total Bytes Sent" + space(11) +
347                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
348                   space(2) +
349                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
350                   space(2) +
351                   str(@tmp_total, 10)
352               print @rptline
353           end /* else */
354           print @blankline
355           print @blankline
356   
357           if @tmp_int2 != 0 /* Avoid divide by zero. */
358           begin
359               select @rptline = "  Avg Bytes Sent per Packet" + space(11) +
360                   @na_str + space(11) +
361                   @na_str + space(2) +
362                   str(@tmp_total / @tmp_int2, 10) +
363                   space(7) +
364                   @na_str
365               print @rptline
366               print @blankline
367           end
368   
369       end /* process mode */
370       else
371   
372       /****************************************************
373       **						
374       ** Threaded mode version is a more consolidated report
375       **
376       *****************************************************/
377       begin
378   
379           print "  Network Receive Activity        per sec      per xact       count"
380           print @sum2line
381   
382           select @tmp_total = SUM(value)
383           from #tempmonitors
384           where group_name = "network" and
385               field_name = "total_packets_received"
386   
387           if @tmp_total = 0
388           begin
389               select @rptline = "  Total TDS Packets Rec'd             0.0           0.0           0"
390               print @rptline
391           end
392           else
393           begin
394               select @rptline = "  Total TDS Packets Rec'd" + space(4) +
395                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
396                   space(2) +
397                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
398                   space(2) +
399                   str(@tmp_total, 10)
400               print @rptline
401           end
402           /* 
403           ** save total packets rec'd for avg bytes / pkt calc 
404           */
405           select @tmp_int2 = @tmp_total
406   
407           select @tmp_total = SUM(value)
408           from #tempmonitors
409           where group_name = "network" and
410               field_name = "total_bytes_received"
411   
412           if @tmp_total = 0
413           begin
414               select @rptline = "  Total Bytes Rec'd                   0.0           0.0           0"
415               print @rptline
416           end
417           else
418           begin
419               select @rptline = "  Total Bytes Rec'd" + space(10) +
420                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
421                   space(2) +
422                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
423                   space(2) +
424                   str(@tmp_total, 10)
425               print @rptline
426           end /* else */
427   
428           if @tmp_int2 != 0 /* Avoid divide by zero. */
429           begin
430               select @rptline = "  Avg Bytes Rec'd per Packet" + space(10) +
431                   @na_str + space(11) +
432                   @na_str + space(2) +
433                   str(@tmp_total / @tmp_int2, 10)
434               print @rptline
435           end
436   
437           print @blankline
438   
439           print "  Network Send Activity           per sec      per xact       count"
440           print @sum2line
441   
442           select @tmp_total = SUM(value)
443           from #tempmonitors
444           where group_name = "network" and
445               field_name = "total_packets_sent"
446   
447           if @tmp_total = 0
448           begin
449               select @rptline = "  Total TDS Packets Sent              0.0           0.0           0       n/a"
450               print @rptline
451           end
452           else
453           begin
454               select @rptline = "  Total TDS Packets Sent" + space(5) +
455                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
456                   space(2) +
457                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
458                   space(2) +
459                   str(@tmp_total, 10)
460               print @rptline
461           end /* else */
462   
463           /* save total packets sent for avg bytes / pkt calc */
464           select @tmp_int2 = @tmp_total
465   
466           select @tmp_total = SUM(value)
467           from #tempmonitors
468           where group_name = "network" and
469               field_name = "total_bytes_sent"
470   
471           if @tmp_total = 0
472           begin
473               select @rptline = "  Total Bytes Sent                    0.0           0.0           0       n/a"
474               print @rptline
475           end
476           else
477           begin
478               select @rptline = "  Total Bytes Sent" + space(11) +
479                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
480                   space(2) +
481                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
482                   space(2) +
483                   str(@tmp_total, 10)
484               print @rptline
485           end /* else */
486   
487           if @tmp_int2 != 0 /* Avoid divide by zero. */
488           begin
489               select @rptline = "  Avg Bytes Sent per Packet" + space(11) +
490                   @na_str + space(11) +
491                   @na_str + space(2) +
492                   str(@tmp_total / @tmp_int2, 10)
493               print @rptline
494           end
495           print @blankline
496       end /* threaded mode */
497   
498       return 0
499   

DEFECTS
 MLCH 3 Char type with length>30 char(80) 19
 MLCH 3 Char type with length>30 char(80) 21
 MLCH 3 Char type with length>30 char(80) 23
 MLCH 3 Char type with length>30 char(80) 27
 MLCH 3 Char type with length>30 char(80) 28
 QAFM 3 Var Assignment from potentially many rows 47
 QAFM 3 Var Assignment from potentially many rows 66
 QAFM 3 Var Assignment from potentially many rows 121
 QAFM 3 Var Assignment from potentially many rows 187
 QAFM 3 Var Assignment from potentially many rows 261
 QAFM 3 Var Assignment from potentially many rows 325
 MSUB 2 Subquery Marker 117
 MSUB 2 Subquery Marker 183
 MSUB 2 Subquery Marker 257
 MSUB 2 Subquery Marker 321
 MTR1 2 Metrics: Comments Ratio Comments: 18% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 42 = 41dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 242 5

DEPENDENCIES
PROCS AND TABLES USED
reads table tempdb..#tempmonitors (1) 

CALLERS
called by proc sybsystemprocs..sp_sysmon_analyze  
   called by proc sybsystemprocs..sp_sysmon