| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_appmgmt | | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report describing the scheduling 3 ** of user tasks. 4 ** 5 */ 6 create procedure sp_sysmon_appmgmt 7 8 @NumEngines tinyint, /* number of engines online */ 9 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 10 @NumXacts int, /* for per transactions calculations */ 11 @applmon char(14) /* Application monitoring option */ 12 as 13 14 /* --------- declare local variables --------- */ 15 declare @i smallint /* loop index to iterate through multi-group 16 ** counters (engine, disk, & buffer) */ 17 declare @tmp_grp varchar(25) /* temp var for building group_names 18 ** ie. engine_N, disk_N */ 19 declare @appl_name varchar(80) /* application name */ 20 declare @tmp_int int /* temp var for integer storage */ 21 declare @tmp_total int /* temp var for integer storage, totals */ 22 declare @sum1line char(80) /* string to delimit total lines without 23 ** percent calc on printout */ 24 declare @sum2line char(67) /* string to delimit total lines with percent 25 ** calc on printout */ 26 declare @blankline char(1) /* to print blank line */ 27 declare @psign char(3) /* hold a percent sign (%) for print out */ 28 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 29 declare @rptline char(80) /* formatted stats line for print statement */ 30 declare @section char(80) /* string to delimit sections on printout */ 31 declare @subsection char(80) /* string to delimit sections on printout */ 32 declare @applstr char(24) /* application report string */ 33 /* --------- Setup Environment --------- */ 34 set nocount on /* disable row counts being sent to client */ 35 36 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 37 select @sum2line = " ------------------------- ------------ ------------ ----------" 38 select @blankline = " " 39 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 40 select @na_str = "n/a" 41 select @section = "===============================================================================" 42 select @subsection = "-------------------------------------------------------------------------------" 43 44 45 /* ========================= Application Management Section ==================== */ 46 if not exists (select * 47 from #tempmonitors 48 where group_name = "kernel" and 49 field_name = "priority_med") 50 begin 51 print @blankline 52 return 0 53 end 54 55 print @section 56 print @blankline 57 print "Application Management" 58 print "----------------------" 59 print @blankline 60 /* 61 ** ------ 62 */ 63 print " Application Statistics Summary (All Applications)" 64 print " -------------------------------------------------" 65 print " Priority Changes per sec per xact count %% of total" 66 print @sum1line 67 select @tmp_total = sum(value) 68 from #tempmonitors 69 where group_name like "kernel" 70 and field_name in ("priority_high", "priority_med", "priority_low") 71 72 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 73 begin 74 select @rptline = " Total Priority Changes 0.0 0.0 0 n/a" 75 print @rptline 76 print @blankline 77 end 78 else 79 begin 80 81 select @tmp_int = value 82 from #tempmonitors 83 where group_name = "kernel" and 84 field_name = "priority_high" 85 86 select @rptline = " To High Priority" + space(9) + 87 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 88 space(2) + 89 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 90 space(2) + 91 str(@tmp_int, 10) + space(5) + 92 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 93 @psign 94 print @rptline 95 select @tmp_int = value 96 from #tempmonitors 97 where group_name = "kernel" and 98 field_name = "priority_med" 99 100 select @rptline = " To Medium Priority" + space(7) + 101 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 102 space(2) + 103 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 104 space(2) + 105 str(@tmp_int, 10) + space(5) + 106 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 107 @psign 108 print @rptline 109 select @tmp_int = value 110 from #tempmonitors 111 where group_name = "kernel" and 112 field_name = "priority_low" 113 114 select @rptline = " To Low Priority" + space(10) + 115 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 116 space(2) + 117 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 118 space(2) + 119 str(@tmp_int, 10) + space(5) + 120 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 121 @psign 122 123 print @rptline 124 125 print @sum2line 126 select @rptline = " Total Priority Changes" + space(5) + 127 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 128 space(2) + 129 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 130 space(2) + 131 str(@tmp_total, 10) 132 print @rptline 133 print @blankline 134 end 135 136 select @tmp_total = sum(value) 137 from #tempmonitors 138 where group_name like "kernel" 139 and field_name in ("tslice_high", "tslice_med", "tslice_low") 140 141 print " Allotted Slices Exhausted per sec per xact count %% of total" 142 print @sum1line 143 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 144 begin 145 select @rptline = " Total Slices Exhausted 0.0 0.0 0 n/a" 146 print @rptline 147 end 148 else 149 begin 150 select @tmp_int = value 151 from #tempmonitors 152 where group_name = "kernel" and 153 field_name = "tslice_high" 154 155 select @rptline = " High Priority" + space(12) + 156 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 157 space(2) + 158 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 159 space(2) + 160 str(@tmp_int, 10) + space(5) + 161 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 162 @psign 163 print @rptline 164 select @tmp_int = value 165 from #tempmonitors 166 where group_name = "kernel" and 167 field_name = "tslice_med" 168 169 select @rptline = " Medium Priority" + space(10) + 170 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 171 space(2) + 172 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 173 space(2) + 174 str(@tmp_int, 10) + space(5) + 175 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 176 @psign 177 print @rptline 178 select @tmp_int = value 179 from #tempmonitors 180 where group_name = "kernel" and 181 field_name = "tslice_low" 182 183 select @rptline = " Low Priority" + space(13) + 184 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 185 space(2) + 186 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 187 space(2) + 188 str(@tmp_int, 10) + space(5) + 189 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 190 @psign 191 print @rptline 192 193 print @sum2line 194 select @rptline = " Total Slices Exhausted " + space(3) + 195 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 196 space(2) + 197 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 198 space(2) + 199 str(@tmp_total, 10) 200 print @rptline 201 end 202 print @blankline 203 print " Skipped Tasks By Engine per sec per xact count %% of total" 204 print @sum1line 205 206 select @tmp_total = SUM(value) 207 from #tempmonitors 208 where group_name like "engine_%" and 209 field_name = "incompatible_engine_task" 210 211 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 212 begin 213 select @rptline = " Total Engine Skips 0.0 0.0 0 n/a" 214 print @rptline 215 end 216 else 217 begin 218 select @i = 0 219 while @i < @NumEngines /* for each engine */ 220 begin 221 /* build group_name string */ 222 select @tmp_grp = "engine_" + convert(varchar(2), @i) 223 select @tmp_int = value 224 from #tempmonitors 225 where group_name = @tmp_grp and 226 field_name = "incompatible_engine_task" 227 228 select @rptline = " Engine " + convert(char(2), @i) + 229 space(16) + 230 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 231 + space(2) + 232 str(@tmp_int / convert(real, @NumXacts), 12, 1) 233 + space(2) + 234 str(@tmp_int, 10) + space(5) + 235 str(100.0 * @tmp_int / @tmp_total, 5, 1) 236 + @psign 237 print @rptline 238 select @i = @i + 1 239 end 240 241 if @NumEngines > 1 242 begin 243 print @sum2line 244 select @rptline = " Total Engine Skips " + space(8) + 245 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 246 + space(2) + 247 str(@tmp_total / convert(real, @NumXacts), 12, 1) 248 + space(2) + str(@tmp_total, 10) 249 print @rptline 250 end 251 end 252 print @blankline 253 254 select @tmp_int = value 255 from #tempmonitors 256 where group_name = "kernel" and 257 field_name = "calls_to_upsetenginemask" 258 259 select @rptline = " Engine Scope Changes" + space(7) + 260 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 261 space(2) + 262 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 263 space(2) + 264 str(@tmp_int, 10) + space(7) + 265 @na_str 266 print @rptline 267 print @blankline 268 269 /* 270 ** If application monitoring was not requested 271 ** return, otherwise, create a temporary table 272 ** of application counters, based on application 273 ** monitoring option 274 */ 275 276 if (@applmon = "no_appl") 277 begin 278 return 0 279 end 280 else 281 begin 282 select field_name, group_name, value 283 into #tempappl 284 from #tempmonitors 285 where 1 = 2 286 287 if (@applmon = "appl_only") 288 begin 289 insert into #tempappl 290 select isnull(substring(field_name, 1, 291 patindex("%->%", field_name) - 1), 292 "Unknown") 293 as field_name, group_name, value 294 from #tempmonitors 295 where group_name like "appl_%" and field_name is not null 296 297 select @applstr = space(2) + "Application: " 298 end 299 300 else if (@applmon = "appl_and_login") 301 begin 302 insert into #tempappl 303 select field_name, group_name, value 304 from #tempmonitors 305 where group_name like "appl_%" and field_name is not null 306 select @applstr = space(2) + "Application->Login: " 307 end 308 end 309 310 /* 311 ** Declare cursor for fetching application specific statistics 312 */ 313 declare appl_stats cursor for 314 select distinct field_name 315 from #tempappl 316 for read only 317 open appl_stats 318 fetch appl_stats into @appl_name 319 320 while (@@sqlstatus = 0) /* { */ 321 begin 322 print @subsection 323 select @rptline = @applstr + @appl_name 324 print @rptline 325 print @blankline 326 print " Application Activity per sec per xact count %% of total" 327 print @sum2line 328 329 select @tmp_total = sum(value) 330 from #tempappl 331 where group_name like "appl_%_ticks%" 332 and field_name = @appl_name 333 334 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 335 begin 336 select @rptline = " Total Activity 0.0 0.0 0 n/a" 337 print @rptline 338 print @blankline 339 end 340 else 341 begin 342 select @tmp_int = sum(value) 343 from #tempappl 344 where group_name like "appl_cpu_ticks%" 345 and field_name = @appl_name 346 347 select @rptline = " CPU Busy" + space(17) + 348 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 349 space(2) + 350 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 351 space(2) + 352 str(@tmp_int, 10) + space(5) + 353 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 354 print @rptline 355 select @tmp_int = sum(value) 356 from #tempappl 357 where group_name like "appl_io_ticks%" 358 and field_name = @appl_name 359 select @rptline = " I/O Busy" + space(17) + 360 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 361 space(2) + 362 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 363 space(2) + 364 str(@tmp_int, 10) + space(5) + 365 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 366 print @rptline 367 select @tmp_int = sum(value) 368 from #tempappl 369 where group_name like "appl_idle_ticks%" 370 and field_name = @appl_name 371 select @rptline = " Idle " + space(17) + 372 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 373 space(2) + 374 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 375 space(2) + 376 str(@tmp_int, 10) + space(5) + 377 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 378 print @rptline 379 end 380 381 print @blankline 382 select @tmp_int = sum(value) 383 from #tempappl 384 where group_name like "appl_sched_count%" 385 and field_name = @appl_name 386 387 select @rptline = " Number of Times Scheduled" + 388 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 389 space(2) + 390 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 391 space(2) + 392 str(@tmp_int, 10) + space(7) + 393 @na_str 394 print @rptline 395 print @blankline 396 397 print " Application Priority Changes per sec per xact count %% of total" 398 print @sum1line 399 select @tmp_total = sum(value) 400 from #tempappl 401 where group_name like "appl_%_pri%" 402 and field_name = @appl_name 403 404 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 405 begin 406 select @rptline = " Total Priority Changes 0.0 0.0 0 n/a" 407 print @rptline 408 print @blankline 409 end 410 else 411 begin 412 413 select @tmp_int = value 414 from #tempappl 415 where group_name like "appl_hi_pri%" 416 and field_name = @appl_name 417 418 select @rptline = " To High Priority" + space(9) + 419 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 420 space(2) + 421 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 422 space(2) + 423 str(@tmp_int, 10) + space(5) + 424 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 425 @psign 426 print @rptline 427 select @tmp_int = value 428 from #tempappl 429 where group_name like "appl_med_pri%" 430 and field_name = @appl_name 431 432 select @rptline = " To Medium Priority" + space(7) + 433 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 434 space(2) + 435 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 436 space(2) + 437 str(@tmp_int, 10) + space(5) + 438 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 439 @psign 440 print @rptline 441 select @tmp_int = value 442 from #tempappl 443 where group_name like "appl_low_pri%" 444 and field_name = @appl_name 445 446 select @rptline = " To Low Priority" + space(10) + 447 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 448 space(2) + 449 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 450 space(2) + 451 str(@tmp_int, 10) + space(5) + 452 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 453 @psign 454 print @rptline 455 print @sum2line 456 select @rptline = " Total Priority Changes" + space(5) + 457 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 458 space(2) + 459 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 460 space(2) + 461 str(@tmp_total, 10) 462 print @rptline 463 print @blankline 464 end 465 466 print " Application I/Os Completed per sec per xact count %% of total" 467 print @sum1line 468 select @tmp_total = sum(value) 469 from #tempappl 470 where group_name like "appl_%io_count%" 471 and field_name = @appl_name 472 473 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 474 begin 475 select @rptline = " Total I/Os Completed 0.0 0.0 0 n/a" 476 print @rptline 477 print @blankline 478 end 479 else 480 begin 481 482 select @tmp_int = value 483 from #tempappl 484 where group_name like "appl_disk_io_count%" 485 and field_name = @appl_name 486 487 select @rptline = " Disk I/Os Completed" + space(6) + 488 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 489 space(2) + 490 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 491 space(2) + 492 str(@tmp_int, 10) + space(5) + 493 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 494 @psign 495 print @rptline 496 select @tmp_int = value 497 from #tempappl 498 where group_name like "appl_net_io_count%" 499 and field_name = @appl_name 500 501 select @rptline = " Network I/Os Completed" + space(3) + 502 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 503 space(2) + 504 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 505 space(2) + 506 str(@tmp_int, 10) + space(5) + 507 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 508 @psign 509 print @rptline 510 print @sum2line 511 select @rptline = " Total I/Os Completed" + space(7) + 512 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 513 space(2) + 514 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 515 space(2) + 516 str(@tmp_total, 10) 517 print @rptline 518 print @blankline 519 520 end 521 522 523 print " Resource Limits Violated per sec per xact count %% of total" 524 print @sum1line 525 526 select @tmp_total = sum(value) 527 from #tempappl 528 where group_name like "%appl_%_lim_xcd%" 529 and field_name = @appl_name 530 531 if (@tmp_total = 0) or (@tmp_total is null) /* Avoid divide by zero errors - just print zero's */ 532 begin 533 select @rptline = " Total Limits Violated 0.0 0.0 0 n/a" 534 print @rptline 535 print @blankline 536 end 537 else 538 begin 539 540 select @tmp_int = sum(value) 541 from #tempappl 542 where group_name like "appl_est_io_lim_xcd%" 543 and field_name = @appl_name 544 545 select @rptline = " IO Limit Violations" 546 print @rptline 547 select @rptline = " Estimated" + space(14) + 548 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 549 space(2) + 550 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 551 space(2) + 552 str(@tmp_int, 10) + space(5) + 553 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 554 print @rptline 555 556 557 select @tmp_int = sum(value) 558 from #tempappl 559 where group_name like "appl_act_io_lim_xcd%" 560 and field_name = @appl_name 561 562 select @rptline = " Actual" + space(17) + 563 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 564 space(2) + 565 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 566 space(2) + 567 str(@tmp_int, 10) + space(5) + 568 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 569 print @rptline 570 print @blankline 571 572 573 select @tmp_int = sum(value) 574 from #tempappl 575 where group_name like "appl_batch_et_lim_xcd%" 576 and field_name = @appl_name 577 578 select @rptline = " Time Limit Violations" 579 print @rptline 580 581 select @rptline = " Batch" + space(18) + 582 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 583 space(2) + 584 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 585 space(2) + 586 str(@tmp_int, 10) + space(5) + 587 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 588 print @rptline 589 590 591 select @tmp_int = sum(value) 592 from #tempappl 593 where group_name like "appl_xact_et_lim_xcd%" 594 and field_name = @appl_name 595 596 select @rptline = " Xact" + space(19) + 597 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 598 space(2) + 599 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 600 space(2) + 601 str(@tmp_int, 10) + space(5) + 602 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 603 print @rptline 604 print @blankline 605 606 607 select @tmp_int = sum(value) 608 from #tempappl 609 where group_name like "appl_rowcount_lim_xcd%" 610 and field_name = @appl_name 611 612 select @rptline = " RowCount Limit Violations" + 613 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 614 space(2) + 615 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 616 space(2) + 617 str(@tmp_int, 10) + space(5) + 618 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 619 print @rptline 620 621 print @sum2line 622 select @rptline = " Total Limits Violated" + space(6) + 623 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 624 space(2) + 625 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 626 space(2) + 627 str(@tmp_total, 10) 628 print @rptline 629 print @blankline 630 end 631 632 fetch appl_stats into @appl_name 633 634 end 635 return 0 636
exec sp_procxmode 'sp_sysmon_appmgmt', 'AnyMode' go Grant Execute on sp_sysmon_appmgmt to public go
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#tempappl (1) reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
