| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_netio | | 31 Aug 14 | Defects 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  |