DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_netio  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** network activity.
4     */
5     create 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(2), @i)
112   
113                   select @tmp_int = value
114                   from #tempmonitors
115                   where group_name = @tmp_grp and
116                       field_name = "no_packets_received"
117   
118                   select @rptline = "    Engine " + convert(char(2), @i) +
119                       space(16) +
120                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
121                       space(2) +
122                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
123                       space(2) +
124                       str(@tmp_int, 10) + space(5) +
125                       str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
126                   print @rptline
127   
128                   select @i = @i + 1
129               end /* while loop */
130   
131               print @sum2line
132   
133               select @rptline = "  Total TDS Packets Rec'd" + space(4) +
134                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
135                   space(2) +
136                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
137                   space(2) +
138                   str(@tmp_total, 10)
139               print @rptline
140           end
141           print @blankline
142           print @blankline
143   
144           /* 
145           ** save total packets rec'd for avg bytes / pkt calc 
146           */
147           select @tmp_int2 = @tmp_total
148   
149           print "  Total Bytes Received            per sec      per xact       count  %% of total"
150           print @sum1line
151   
152           select @tmp_total = SUM(value)
153           from #tempmonitors
154           where group_name like "engine_%" and
155               field_name = "no_bytes_received"
156   
157           if @tmp_total = 0
158           begin
159               select @rptline = "  Total Bytes Rec'd                   0.0           0.0           0       n/a"
160               print @rptline
161           end
162           else
163           begin
164               select @i = 0
165               while @i < @NumEngines /* for each engine */
166               begin
167                   /* build group_name string */
168                   select @tmp_grp = "engine_" + convert(varchar(2), @i)
169   
170                   select @tmp_int = value
171                   from #tempmonitors
172                   where group_name = @tmp_grp and
173                       field_name = "no_bytes_received"
174   
175                   select @rptline = "    Engine " + convert(char(2), @i) +
176                       space(16) +
177                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
178                       space(2) +
179                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
180                       space(2) +
181                       str(@tmp_int, 10) + space(5) +
182                       str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
183                   print @rptline
184   
185                   select @i = @i + 1
186               end /* while loop */
187   
188               print @sum2line
189   
190               select @rptline = "  Total Bytes Rec'd" + space(10) +
191                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
192                   space(2) +
193                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
194                   space(2) +
195                   str(@tmp_total, 10)
196               print @rptline
197           end /* else */
198           print @blankline
199           print @blankline
200   
201           if @tmp_int2 != 0 /* Avoid divide by zero. */
202           begin
203               select @rptline = "   Avg Bytes Rec'd per Packet" + space(10) +
204                   @na_str + space(11) +
205                   @na_str + space(2) +
206                   str(@tmp_total / @tmp_int2, 10) + space(7) +
207                   @na_str
208               print @rptline
209               print @blankline
210           end
211           print @subsection
212           print @blankline
213   
214           print "  Total TDS Packets Sent          per sec      per xact       count  %% of total"
215           print @sum1line
216   
217           select @tmp_total = SUM(value)
218           from #tempmonitors
219           where group_name like "engine_%" and
220               field_name = "no_packets_sent"
221   
222           if @tmp_total = 0
223           begin
224               select @rptline = "  Total TDS Packets Sent              0.0           0.0           0       n/a"
225               print @rptline
226           end
227           else
228           begin
229               select @i = 0
230               while @i < @NumEngines /* for each engine */
231               begin
232                   /* build group_name string */
233                   select @tmp_grp = "engine_" + convert(varchar(2), @i)
234   
235                   select @tmp_int = value
236                   from #tempmonitors
237                   where group_name = @tmp_grp and
238                       field_name = "no_packets_sent"
239   
240                   select @rptline = "    Engine " + convert(char(2), @i) +
241                       space(16) +
242                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
243                       space(2) +
244                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
245                       space(2) +
246                       str(@tmp_int, 10) + space(5) +
247                       str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
248                   print @rptline
249   
250                   select @i = @i + 1
251               end /* while loop */
252   
253               print @sum2line
254   
255               select @rptline = "  Total TDS Packets Sent" + space(5) +
256                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
257                   space(2) +
258                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
259                   space(2) +
260                   str(@tmp_total, 10)
261               print @rptline
262           end /* else */
263           print @blankline
264           print @blankline
265   
266           /* save total packets sent for avg bytes / pkt calc */
267           select @tmp_int2 = @tmp_total
268   
269           print "  Total Bytes Sent                per sec      per xact       count  %% of total"
270           print @sum1line
271   
272           select @tmp_total = SUM(value)
273           from #tempmonitors
274           where group_name like "engine_%" and
275               field_name = "no_bytes_sent"
276   
277           if @tmp_total = 0
278           begin
279               select @rptline = "  Total Bytes Sent                    0.0           0.0           0       n/a"
280               print @rptline
281           end
282           else
283           begin
284               select @i = 0
285               while @i < @NumEngines /* for each engine */
286               begin
287                   /* build group_name string */
288                   select @tmp_grp = "engine_" + convert(varchar(2), @i)
289   
290                   select @tmp_int = value
291                   from #tempmonitors
292                   where group_name = @tmp_grp and
293                       field_name = "no_bytes_sent"
294   
295                   select @rptline = "    Engine " + convert(char(2), @i) +
296                       space(16) +
297                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
298                       space(2) +
299                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
300                       space(2) +
301                       str(@tmp_int, 10) + space(5) +
302                       str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
303                   print @rptline
304   
305                   select @i = @i + 1
306               end /* while loop */
307   
308               print @sum2line
309   
310               select @rptline = "  Total Bytes Sent" + space(11) +
311                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
312                   space(2) +
313                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
314                   space(2) +
315                   str(@tmp_total, 10)
316               print @rptline
317           end /* else */
318           print @blankline
319           print @blankline
320   
321           if @tmp_int2 != 0 /* Avoid divide by zero. */
322           begin
323               select @rptline = "  Avg Bytes Sent per Packet" + space(11) +
324                   @na_str + space(11) +
325                   @na_str + space(2) +
326                   str(@tmp_total / @tmp_int2, 10) +
327                   space(7) +
328                   @na_str
329               print @rptline
330               print @blankline
331           end
332   
333       end /* process mode */
334       else
335   
336       /****************************************************
337       **						
338       ** Threaded mode version is a more consolidated report
339       **
340       *****************************************************/
341       begin
342   
343           print "  Network Receive Activity        per sec      per xact       count"
344           print @sum2line
345   
346           select @tmp_total = SUM(value)
347           from #tempmonitors
348           where group_name = "network" and
349               field_name = "total_packets_received"
350   
351           if @tmp_total = 0
352           begin
353               select @rptline = "  Total TDS Packets Rec'd             0.0           0.0           0"
354               print @rptline
355           end
356           else
357           begin
358               select @rptline = "  Total TDS Packets Rec'd" + space(4) +
359                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
360                   space(2) +
361                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
362                   space(2) +
363                   str(@tmp_total, 10)
364               print @rptline
365           end
366           /* 
367           ** save total packets rec'd for avg bytes / pkt calc 
368           */
369           select @tmp_int2 = @tmp_total
370   
371           select @tmp_total = SUM(value)
372           from #tempmonitors
373           where group_name = "network" and
374               field_name = "total_bytes_received"
375   
376           if @tmp_total = 0
377           begin
378               select @rptline = "  Total Bytes Rec'd                   0.0           0.0           0"
379               print @rptline
380           end
381           else
382           begin
383               select @rptline = "  Total Bytes Rec'd" + space(10) +
384                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
385                   space(2) +
386                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
387                   space(2) +
388                   str(@tmp_total, 10)
389               print @rptline
390           end /* else */
391   
392           if @tmp_int2 != 0 /* Avoid divide by zero. */
393           begin
394               select @rptline = "  Avg Bytes Rec'd per Packet" + space(10) +
395                   @na_str + space(11) +
396                   @na_str + space(2) +
397                   str(@tmp_total / @tmp_int2, 10)
398               print @rptline
399           end
400   
401           print @blankline
402   
403           print "  Network Send Activity           per sec      per xact       count"
404           print @sum2line
405   
406           select @tmp_total = SUM(value)
407           from #tempmonitors
408           where group_name = "network" and
409               field_name = "total_packets_sent"
410   
411           if @tmp_total = 0
412           begin
413               select @rptline = "  Total TDS Packets Sent              0.0           0.0           0       n/a"
414               print @rptline
415           end
416           else
417           begin
418               select @rptline = "  Total TDS Packets Sent" + space(5) +
419                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
420                   space(2) +
421                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
422                   space(2) +
423                   str(@tmp_total, 10)
424               print @rptline
425           end /* else */
426   
427           /* save total packets sent for avg bytes / pkt calc */
428           select @tmp_int2 = @tmp_total
429   
430           select @tmp_total = SUM(value)
431           from #tempmonitors
432           where group_name = "network" and
433               field_name = "total_bytes_sent"
434   
435           if @tmp_total = 0
436           begin
437               select @rptline = "  Total Bytes Sent                    0.0           0.0           0       n/a"
438               print @rptline
439           end
440           else
441           begin
442               select @rptline = "  Total Bytes Sent" + space(11) +
443                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
444                   space(2) +
445                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
446                   space(2) +
447                   str(@tmp_total, 10)
448               print @rptline
449           end /* else */
450   
451           if @tmp_int2 != 0 /* Avoid divide by zero. */
452           begin
453               select @rptline = "  Avg Bytes Sent per Packet" + space(11) +
454                   @na_str + space(11) +
455                   @na_str + space(2) +
456                   str(@tmp_total / @tmp_int2, 10)
457               print @rptline
458           end
459           print @blankline
460       end /* threaded mode */
461   
462       return 0
463   


exec sp_procxmode 'sp_sysmon_netio', 'AnyMode'
go

Grant Execute on sp_sysmon_netio to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_netio  
 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 113
 QAFM 3 Var Assignment from potentially many rows 170
 QAFM 3 Var Assignment from potentially many rows 235
 QAFM 3 Var Assignment from potentially many rows 290
 MTR1 2 Metrics: Comments Ratio Comments: 15% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 33 = 32dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 222 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