| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_taskmgmt | | 31 Aug 14 | Defects Dependencies |
1 2 3 create procedure sp_sysmon_taskmgmt 4 5 @NumEngines tinyint, /* number of engines online */ 6 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 7 @NumXacts int, /* for per transactions calculations */ 8 @Reco char(1) /* Flag for recommendations */ 9 as 10 11 /* --------- declare local variables --------- */ 12 declare @NumTaskSwitch int, /* Total Number of Task Context Switches 13 ** across all engines */ 14 @IgnoreTaskSwitch int, /* Total Number of Task Context Switches 15 ** which can be ignored */ 16 @KnownTaskSwitch int, /* Count of Number of Task Context Switches 17 ** by Known Causes */ 18 @IgnoreTaskYields int, /* Total Number of Task Yields which can be 19 ** ignored */ 20 @i smallint, /* loop index to iterate through multi-group 21 ** counters (engine, disk, & buffer) */ 22 @tmp_grp varchar(25), /* temp var for building group_names 23 ** ie. engine_N, disk_N */ 24 @tmp_int int, /* temp var for integer storage */ 25 @tmp_tot int, /* temp var for integer storage */ 26 @tmp_iopacing int, /* temp var for storing the value of I/O pacing */ 27 @sum1line char(80), /* string to delimit total lines without 28 ** percent calc on printout */ 29 @sum2line char(67), /* string to delimit total lines with percent 30 ** calc on printout */ 31 @blankline char(1), /* to print blank line */ 32 @psign char(3), /* hold a percent sign (%) for print out */ 33 @na_str char(3), /* holds 'n/a' for 'not applicable' strings */ 34 @rptline char(80), /* formatted stats line for print statement */ 35 @section char(80), /* string to delimit sections on printout */ 36 37 /* ------------- threaded mode variables -------------*/ 38 @EngineId int, /* Engine Id corresponding to thread */ 39 @ThreadId int, /* Thread ID*/ 40 @TpId int, /* Thread Pool ID */ 41 @thr_count int, /* number of threads */ 42 @TpName varchar(80), /* ThreadPool Name */ 43 44 /* ------------- Variables for Tuning Recommendations ------------*/ 45 @recotxt char(80), 46 @recoline char(80), 47 @reco_hdr_prn bit, 48 @reco_cache_search_miss real, 49 @reco_io_pacing real, 50 @reco_lock_contention real, 51 @reco_group_commit_sleeps real, 52 @reco_device_contention real, 53 @reco_network_sent real, 54 @reco_network_received real 55 56 57 /* --------- Setup Environment --------- */ 58 set nocount on /* disable row counts being sent to client */ 59 60 select @sum1line = "--------------------------- ------------ ------------ ---------- ----------" 61 select @sum2line = " ------------------------- ------------ ------------ ----------" 62 select @blankline = " " 63 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 64 select @na_str = "n/a" 65 select @section = "===============================================================================" 66 67 /* ========================= Task Management Section ==================== */ 68 print @section 69 print @blankline 70 print "Task Management per sec per xact count %% of total" 71 print @sum1line 72 print @blankline 73 /* 74 ** ------ Connections Opened (Closed Not Available in counters) ------ 75 */ 76 select @tmp_int = value 77 from #tempmonitors 78 where group_name = "kernel" and 79 field_name = "processes_created" 80 81 select @rptline = " Connections Opened" + space(9) 82 + str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 83 + space(2) 84 + str(@tmp_int / convert(real, @NumXacts), 12, 1) 85 + space(2) 86 + str(@tmp_int, 10) + space(7) 87 + @na_str 88 print @rptline 89 print @blankline 90 91 select @NumTaskSwitch = SUM(value) 92 from #tempmonitors 93 where group_name like "engine_%" and 94 field_name = "context_switches" 95 96 97 /* 98 ** Count the number of task switches which can be ignored. For example, 99 ** BCM/CLM daemon may be woken up when ever a request is queued. Once 100 ** the request is serviced, daemons goes back to sleep. Similarly sleeps 101 ** due to garbage collector can also be ignored. 102 */ 103 select @IgnoreTaskSwitch = 0 104 105 select @tmp_int = value 106 from #tempmonitors 107 where group_name = "lock" and 108 field_name = "lock_gc_yields" 109 110 select @IgnoreTaskSwitch = @IgnoreTaskSwitch + @tmp_int 111 112 113 select @tmp_int = value 114 from #tempmonitors 115 where group_name = "lock" and 116 field_name = "daemon_context_switches" 117 118 select @IgnoreTaskSwitch = @IgnoreTaskSwitch + @tmp_int 119 120 select @tmp_int = SUM(value) 121 from #tempmonitors 122 where group_name = "bcmt" and 123 field_name in ("bcmt_pri_sleeps", "bcmt_sec_sleeps") 124 125 select @IgnoreTaskSwitch = @IgnoreTaskSwitch + @tmp_int 126 127 select @IgnoreTaskYields = value 128 from #tempmonitors 129 where group_name = "bcmt" and 130 field_name = "bcmt_sec_empty_scans" 131 132 print " Task Context Switches by Engine" 133 134 if @NumTaskSwitch <= 0 /* Avoid divide by zero errors - print zero's */ 135 begin 136 select @rptline = " Total Task Switches 0.0 0.0 0 n/a" 137 print @rptline 138 end 139 else 140 begin 141 if (@@kernelmode = 'process') 142 begin 143 select @i = 0 144 while @i < @NumEngines /* for each engine */ 145 begin 146 /* build group_name string */ 147 select @tmp_grp = "engine_" + convert(varchar(3), @i) 148 149 select @tmp_int = value 150 from #tempmonitors 151 where group_name = @tmp_grp and 152 field_name = "context_switches" 153 154 select @rptline = " Engine " + convert(char(4), @i) + space(14) + 155 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 156 space(2) + 157 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 158 space(2) + 159 str(@tmp_int, 10) + space(5) + 160 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 161 @psign 162 print @rptline 163 select @i = @i + 1 164 end /* while */ 165 end /* process mode */ 166 else 167 begin 168 print @blankline 169 170 /* threaded mode */ 171 select ThreadPoolID, ThreadPoolName, Size, Type 172 into #tmpThreadPool 173 from master.dbo.monThreadPool 174 order by ThreadPoolName 175 176 declare epcursor cursor for 177 select ThreadPoolID, ThreadPoolName, Size 178 from #tmpThreadPool 179 where Type = "Engine (Multiplexed)" 180 order by ThreadPoolName 181 182 declare engcursor cursor for 183 select engineid, enginename 184 from #muxthreadsinfo 185 where tpname = @TpName 186 order by engineid 187 188 open epcursor 189 fetch epcursor into @TpId, @TpName, @thr_count 190 while (@@sqlstatus = 0) 191 begin 192 select @rptline = " ThreadPool : " + @TpName 193 print @rptline 194 195 select @tmp_tot = 0 196 197 open engcursor 198 fetch engcursor into @EngineId, @tmp_grp 199 while (@@sqlstatus = 0) 200 begin 201 select @tmp_int = value 202 from #tempmonitors 203 where group_name = @tmp_grp and 204 field_name = "context_switches" 205 select @tmp_tot = @tmp_tot + @tmp_int 206 207 select @rptline = " Engine " + convert(char(4), @EngineId) + 208 space(14) + 209 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 210 space(2) + 211 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 212 space(2) + 213 str(@tmp_int, 10) + space(5) + 214 str(100.0 * @tmp_int / (@NumTaskSwitch + 215 @IgnoreTaskSwitch + @IgnoreTaskYields), 5, 1) + 216 @psign 217 print @rptline 218 219 fetch engcursor into @EngineId, @tmp_grp 220 end 221 close engcursor 222 223 /* Print the Pool Average */ 224 if @thr_count > 1 225 begin 226 print @sum2line 227 select @rptline = " Pool Summary" + space(8) 228 + "Total" + space(2) + 229 str(@tmp_tot / (@NumElapsedMs / 1000.0), 12, 1) 230 + space(2) + 231 str(@tmp_tot / convert(real, @NumXacts), 12, 1) 232 + space(2) + 233 str(@tmp_tot, 10) + space(5) 234 print @rptline 235 236 select @rptline = space(20) + "Average" + space(2) + 237 str((@tmp_tot / @thr_count) / (@NumElapsedMs / 1000.0), 12, 1) 238 + space(2) + 239 str((@tmp_tot / @thr_count) / convert(real, @NumXacts), 12, 1) 240 + space(2) + 241 str((@tmp_tot / @thr_count), 10) + space(5) 242 print @rptline 243 end 244 245 print @blankline 246 247 fetch epcursor into @TpId, @TpName, @thr_count 248 end /* loop of pools */ 249 250 close epcursor 251 deallocate cursor epcursor 252 253 end /* threaded mode */ 254 255 if @NumEngines > 1 256 begin 257 print @sum2line 258 select @rptline = " Total Task Switches:" + space(5) + 259 str(@NumTaskSwitch / (@NumElapsedMs / 1000.0), 12, 1) + 260 space(2) + 261 str(@NumTaskSwitch / convert(real, @NumXacts), 12, 1) + 262 space(2) + 263 str(@NumTaskSwitch, 10) 264 print @rptline 265 end /* if @NumEngines > 1*/ 266 267 print @blankline 268 269 select @NumTaskSwitch = @NumTaskSwitch - @IgnoreTaskSwitch - @IgnoreTaskYields 270 271 /* 272 ** Break Down All Task Context Switches by Cause 273 */ 274 print " Task Context Switches Due To:" 275 276 /* init count of known context switches */ 277 select @KnownTaskSwitch = 0 278 279 /* 280 ** Context Switch Due to Task Yields (Voluntary) 281 */ 282 select @tmp_int = value 283 from #tempmonitors 284 where group_name = "kernel" and 285 field_name = "yields" 286 287 select @tmp_int = @tmp_int - @IgnoreTaskYields 288 289 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 290 291 select @rptline = " Voluntary Yields" + space(9) + 292 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 293 space(2) + 294 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 295 space(2) + 296 str(@tmp_int, 10) + space(5) + 297 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 298 @psign 299 print @rptline 300 301 /* 302 ** Context Switch Due to Cache Search Misses resulting in a read 303 */ 304 select @tmp_int = SUM(value) 305 from #tempmonitors 306 where group_name like "buffer_%" and 307 field_name = "bufread_read_waits" 308 309 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 310 311 select @rptline = " Cache Search Misses" + space(6) + 312 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 313 space(2) + 314 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 315 space(2) + 316 str(@tmp_int, 10) + space(5) + 317 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 318 @psign 319 320 select @reco_cache_search_miss = convert(int, 321 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 322 323 print @rptline 324 325 /* 326 ** Context Switch due to exceedint the 'i/o batch size' config limit. 327 ** 328 ** In other words, we started I/O batch and now we are waiting 329 ** for them to complete before starting the next batch. The server 330 ** works in batches to avoid flooding the I/O subsystem. The size 331 ** of the batch is tuneable via config parameter 'io batch size'. 332 */ 333 select @tmp_int = SUM(value) 334 from #tempmonitors 335 where group_name like "buffer_%" and 336 field_name in ("my_start_waits_periobatch") 337 338 select @tmp_iopacing = @tmp_int 339 340 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 341 342 select @rptline = " Exceeding I/O batch size" + space(1) + 343 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 344 space(2) + 345 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 346 space(2) + 347 str(@tmp_int, 10) + space(5) + 348 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 349 @psign 350 351 select @reco_io_pacing = convert(int, 352 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 353 354 print @rptline 355 356 /* 357 ** Context Switch Due to Disk Writes 358 */ 359 select @tmp_int = SUM(value) 360 from #tempmonitors 361 where group_name like "buffer_%" and 362 field_name in ("write_waits", "hk_write_waits", 363 "restart_io_waits", 364 "my_start_waits_log", 365 "my_start_waits_non-log", 366 "my_other_waits_non-log") 367 368 select @tmp_int = @tmp_int - @tmp_iopacing 369 370 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 371 372 select @rptline = " System Disk Writes" + space(7) + 373 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 374 space(2) + 375 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 376 space(2) + 377 str(@tmp_int, 10) + space(5) + 378 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 379 @psign 380 print @rptline 381 382 /* 383 ** Context Switch Due to DB Lock Contention 384 */ 385 select @tmp_int = SUM(value) 386 from #tempmonitors 387 where group_name = "lock" and 388 field_name like "waited_%" and 389 field_name not like "waited_%_ADDR" 390 391 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 392 393 select @rptline = " Logical Lock Contention " + 394 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 395 space(2) + 396 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 397 space(2) + 398 str(@tmp_int, 10) + space(5) + 399 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 400 @psign 401 402 select @reco_lock_contention = convert(int, 403 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 404 405 print @rptline 406 407 /* 408 ** Context Switch Due to Address Lock Contention 409 */ 410 select @tmp_int = SUM(value) 411 from #tempmonitors 412 where group_name = "lock" and 413 field_name like "waited_%_ADDR" 414 415 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 416 417 select @rptline = " Address Lock Contention " + 418 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 419 space(2) + 420 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 421 space(2) + 422 str(@tmp_int, 10) + space(5) + 423 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 424 @psign 425 print @rptline 426 427 /* 428 ** Context Switch Due to Latch Contention 429 */ 430 select @tmp_int = SUM(value) 431 from #tempmonitors 432 where group_name = "latch" and 433 field_name like "waited_%_LATCH" 434 435 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 436 437 select @rptline = " Latch Contention " + space(7) + 438 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 439 space(2) + 440 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 441 space(2) + 442 str(@tmp_int, 10) + space(5) + 443 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 444 @psign 445 446 print @rptline 447 448 /* Context Switch Due to Physical lock transition. */ 449 select @tmp_int = SUM(value) 450 from #tempmonitors 451 where group_name = "lock" and 452 field_name like "physical_lock_context_switches" 453 454 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 455 456 select @rptline = " Physical Lock Transition" + space(1) + 457 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 458 space(2) + 459 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 460 space(2) + 461 str(@tmp_int, 10) + space(5) + 462 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 463 @psign 464 465 print @rptline 466 467 /* Context Switch Due to Logical Lock Transition. */ 468 select @tmp_int = SUM(value) 469 from #tempmonitors 470 where group_name = "lock" and 471 field_name like "logical_lock_context_switches" 472 473 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 474 475 select @rptline = " Logical Lock Transition" + space(2) + 476 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 477 space(2) + 478 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 479 space(2) + 480 str(@tmp_int, 10) + space(5) + 481 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 482 @psign 483 484 print @rptline 485 486 /* Context Switch Due to Object Lock Transition. */ 487 select @tmp_int = SUM(value) 488 from #tempmonitors 489 where group_name = "lock" and 490 field_name like "ocm_context_switches" 491 492 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 493 494 select @rptline = " Object Lock Transition " + space(2) + 495 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 496 space(2) + 497 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 498 space(2) + 499 str(@tmp_int, 10) + space(5) + 500 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 501 @psign 502 503 print @rptline 504 505 /* 506 ** Context Switch Due to Blocking on Log Semaphore 507 */ 508 select @tmp_int = value 509 from #tempmonitors 510 where group_name = "xls" and 511 field_name = "log_lock_waited" 512 513 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 514 515 select @rptline = " Log Semaphore Contention " + 516 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 517 space(2) + 518 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 519 space(2) + 520 str(@tmp_int, 10) + space(5) + 521 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 522 @psign 523 print @rptline 524 525 /* 526 ** Context Switch Due to Blocking on PLC lock 527 */ 528 select @tmp_int = value 529 from #tempmonitors 530 where group_name = "xls" and 531 field_name = "plc_lock_waits" 532 533 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 534 535 select @rptline = " PLC Lock Contention " + space(5) + 536 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 537 space(2) + 538 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 539 space(2) + 540 str(@tmp_int, 10) + space(5) + 541 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 542 @psign 543 print @rptline 544 545 /* 546 ** Context Switch Due to Group Commit Sleeps 547 */ 548 select @tmp_int = SUM(value) 549 from #tempmonitors 550 where group_name like "buffer_%" and 551 field_name IN ("my_other_waits_log", 552 "log_lastpage_pending_io_sleeps") 553 554 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 555 556 select @rptline = " Group Commit Sleeps" + space(6) + 557 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 558 space(2) + 559 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 560 space(2) + 561 str(@tmp_int, 10) + space(5) + 562 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 563 @psign 564 565 select @reco_group_commit_sleeps = convert(int, 566 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 567 568 print @rptline 569 570 /* 571 ** Context Switch Due to Last Log Page Writes 572 */ 573 select @tmp_int = SUM(value) 574 from #tempmonitors 575 where group_name like "buffer_%" and 576 field_name = "last_log_page_writes" 577 578 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 579 580 select @rptline = " Last Log Page Writes" + space(5) + 581 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 582 space(2) + 583 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 584 space(2) + 585 str(@tmp_int, 10) + space(5) + 586 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 587 @psign 588 print @rptline 589 590 /* Context Switch Due to Modify Conflicts 591 ** 592 ** In other words, a task wants to perform an operation on a page 593 ** (i.e. write it), but can't because another task is in the middle 594 ** of modifying it. 595 */ 596 select @tmp_int = SUM(value) 597 from #tempmonitors 598 where group_name like "buffer_%" and field_name in 599 ("changing_state_waits", "bufwrite_changing_waits", 600 "bufpredirty_write_waits", "bufpredirty_changing_waits", 601 "bufnewpage_changing_waits", "ind_bufguess_changing_waits", 602 "ind_bufguess_writing_waits") 603 604 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 605 606 select @rptline = " Modify Conflicts" + space(9) + 607 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 608 space(2) + 609 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 610 space(2) + 611 str(@tmp_int, 10) + space(5) + 612 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 613 @psign 614 print @rptline 615 616 /* 617 ** Context Switch Due to Disk Device Contention 618 */ 619 select @tmp_int = SUM(value) 620 from #tempmonitors 621 where group_name like "disk_%" and 622 field_name = "p_sleeps" 623 624 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 625 626 select @rptline = " I/O Device Contention" + space(4) + 627 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 628 space(2) + 629 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 630 space(2) + 631 str(@tmp_int, 10) + space(5) + 632 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 633 @psign 634 635 select @reco_device_contention = convert(int, 636 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 637 638 print @rptline 639 640 /* 641 ** Context Switch Due to Network Packets Received 642 */ 643 select @tmp_int = value 644 from #tempmonitors 645 where group_name = "network" and 646 field_name = "network_read_sleeps" 647 648 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 649 650 select @rptline = " Network Packet Received" + space(2) + 651 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 652 space(2) + 653 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 654 space(2) + 655 str(@tmp_int, 10) + space(5) + 656 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 657 @psign 658 659 select @reco_network_received = convert(int, 660 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 661 662 print @rptline 663 664 /* 665 ** Context Switch Due to Network Packets Sent 666 */ 667 select @tmp_int = SUM(value) 668 from #tempmonitors 669 where group_name = "network" and 670 field_name = "network_send_sleeps" 671 672 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 673 674 select @rptline = " Network Packet Sent" + space(6) + 675 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 676 space(2) + 677 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 678 space(2) + 679 str(@tmp_int, 10) + space(5) + 680 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 681 @psign 682 683 select @reco_network_sent = convert(int, 684 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 685 686 print @rptline 687 688 /* Context Switch Due to CIPC Thread Sleeps. */ 689 select @tmp_int = SUM(value) 690 from #tempmonitors 691 where group_name = "kernel" and 692 field_name like "cipc_context_switches" 693 694 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 695 696 select @rptline = " Interconnect Message Sleeps" + space(1) + 697 str(@tmp_int / (@NumElapsedMs / 1000.0), 9, 1) + 698 space(2) + 699 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 700 space(2) + 701 str(@tmp_int, 10) + space(5) + 702 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 703 @psign 704 705 print @rptline 706 707 /* 708 ** Context Switch Due to Network services 709 */ 710 select @tmp_int = SUM(value) 711 from #tempmonitors 712 where group_name like "network%" and 713 field_name in ("nserver_sleeps") 714 715 select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int 716 717 select @rptline = " Network services" + space(9) + 718 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 719 space(2) + 720 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 721 space(2) + 722 str(@tmp_int, 10) + space(5) + 723 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 724 @psign 725 726 select @reco_io_pacing = convert(int, 727 100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch)) 728 729 print @rptline 730 731 /* 732 ** Context Switch Due to Other Causes 733 */ 734 select @tmp_int = @NumTaskSwitch - @KnownTaskSwitch 735 select @rptline = " Other Causes" + space(13) + 736 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 737 space(2) + 738 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 739 space(2) + 740 str(@tmp_int, 10) + space(5) + 741 str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) + 742 @psign 743 print @rptline 744 745 end /* else @NumTaskSwitch != 0 */ 746 747 print @blankline 748 749 if @Reco = 'Y' 750 begin 751 select @recotxt = " Tuning Recommendations for Task Management" 752 select @recoline = " ------------------------------------------" 753 select @reco_hdr_prn = 0 754 755 /* 756 ** If the context switches due to cache misses is > 50% 757 ** consider tuning your Data Caches 758 */ 759 if (@reco_cache_search_miss > 50) 760 begin 761 if (@reco_hdr_prn = 0) 762 begin 763 print @recotxt 764 print @recoline 765 select @reco_hdr_prn = 1 766 end 767 768 print " - Consider tuning your Data Caches." 769 print " Look into the Data Cache management section for" 770 print " more details on this." 771 print @blankline 772 select @reco_hdr_prn = 1 773 end 774 775 /* 776 ** If the context switches due to IO Pacing is > 50% consider tuning 777 ** the 'i/o batch size' configuration parameter. 778 */ 779 if (@reco_io_pacing > 50) 780 begin 781 if (@reco_hdr_prn = 0) 782 begin 783 print @recotxt 784 print @recoline 785 select @reco_hdr_prn = 1 786 end 787 788 print " - Consider tuning the 'i/o batch size' configuration parameter parameter" 789 print " A value of 100 is generally optimal. Also verify the I/O response times if the 'i/o batch size' tuning does not help" 790 print @blankline 791 select @reco_hdr_prn = 1 792 end 793 794 /* 795 ** If context switches due to lock contention is > 50% consider using 796 ** different lock management strategies such as row level locking 797 ** for certain objects. 798 */ 799 if (@reco_lock_contention > 50) 800 begin 801 if (@reco_hdr_prn = 0) 802 begin 803 print @recotxt 804 print @recoline 805 select @reco_hdr_prn = 1 806 end 807 808 print " - Consider identifying objects with high" 809 print " lock contention using the sp_object_stats stored procedure" 810 print " Review the Lock management section for more details." 811 print @blankline 812 select @reco_hdr_prn = 1 813 end 814 /* 815 ** If the context switches due to group commit sleeps is > 50% 816 ** consider tuning the log io size 817 */ 818 if (@reco_group_commit_sleeps > 50) 819 begin 820 if (@reco_hdr_prn = 0) 821 begin 822 print @recotxt 823 print @recoline 824 select @reco_hdr_prn = 1 825 end 826 print " - Consider lowering the logio size using sp_logiosize" 827 print " for better transaction commit performance." 828 print " This recommendation is not valid if the logio size is 2K." 829 print @blankline 830 select @reco_hdr_prn = 1 831 end 832 /* 833 ** If the context switches due to device contention is > 50% 834 ** consider tuning the disk i/o sub system 835 */ 836 if (@reco_device_contention > 50) 837 begin 838 if (@reco_hdr_prn = 0) 839 begin 840 print @recotxt 841 print @recoline 842 select @reco_hdr_prn = 1 843 end 844 845 print " - Consider tuning your Disk I/O sub-system." 846 print @blankline 847 select @reco_hdr_prn = 1 848 end 849 /* 850 ** If the context switches due to the network is > 50% 851 ** consider tuning the network I/O sub-system 852 */ 853 if (@reco_network_sent > 50 OR @reco_network_received > 50) 854 begin 855 if (@reco_hdr_prn = 0) 856 begin 857 print @recotxt 858 print @recoline 859 select @reco_hdr_prn = 1 860 end 861 862 print " - Consider tuning your Network I/O sub-system." 863 print @blankline 864 select @reco_hdr_prn = 1 865 end 866 end 867 print @blankline 868 869 return 0 870
exec sp_procxmode 'sp_sysmon_taskmgmt', 'AnyMode' go Grant Execute on sp_sysmon_taskmgmt to public go
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#tmpThreadPool (1) reads table tempdb..#muxthreadsinfo (1) reads table master..monThreadPool (1)  reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
