| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_kernel_threaded | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_sysmon_kernel_threaded 3 @NumMuxThreads tinyint, /* number of engine threads online */ 4 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 5 @NumXacts int, /* for per transactions calculations */ 6 @Reco char(1), /* Flag for recommendations */ 7 @instid smallint = NULL /* optional SDC instance id */ 8 as 9 10 /* --------- declare local variables --------- */ 11 declare @EngineId int, /* Engine Id corresponding to thread */ 12 @ThreadId int, /* Thread ID*/ 13 @TpId int, /* Thread Pool ID */ 14 @TpName varchar(80), /* ThreadPool Name */ 15 @tmp_grp varchar(25), /* temp var to build group_names 16 ** ie. engine_N, disk_N */ 17 18 @tmp_int int, /* temp var for integer storage */ 19 @tmp_int2 int, /* temp var for integer storage */ 20 @tmp_tot int, /* temp var for summing 'total #s' data */ 21 @tmp_total bigint, /* temp var for summing 'total #s' data */ 22 @tmp_server int, /* temp var for storing server summary */ 23 @cpu_busy real, /* var for cpu busy percentage */ 24 @cpu_busy_sum real, /* var for summing cpu busy percentage */ 25 @cpu_busy_avg real, /* var for averaging cpu busy percentage */ 26 @cpu_busy_server real, /* Total cpu busy of server */ 27 @cpu_server_avg real, /* var for averaging server cpu percentage */ 28 @idle real, /* var for tracking idle value */ 29 @thr_count int, /* number of threads within in a pool */ 30 31 @user_busy real, /* user time percentage */ 32 @user_busy_sum real, /* user time percentage, summed */ 33 @user_busy_avg real, /* user time percentage, average */ 34 @user_busy_server real, /* user time for all threads */ 35 @system_busy real, /* system time percentage */ 36 @system_busy_sum real, /* system time percentage, summed */ 37 @system_busy_avg real, /* system time percentage, average */ 38 @system_busy_server real, /* system time for all threads */ 39 40 @tmp_bigint1 bigint, 41 @tmp_bigint2 bigint, 42 @tmp_real1 real, 43 @tmp_real2 real, 44 @tmp_real3 real, 45 @real_tot real, 46 47 @TaskName varchar(30), /* Task thread is running */ 48 49 /* useful variables for printing */ 50 @sum1line char(80), /* string to delimit total lines without 51 ** percent calc on printout */ 52 @sum2line char(80), /* string to delimit total lines with percent 53 ** calc on printout */ 54 @blankline char(1), /* to print blank line */ 55 @psign char(3), /* hold a percent sign (%) for print out */ 56 @na_str char(3), /* holds 'n/a' for 'not applicable' strings */ 57 @rptline char(80), /* formatted statistics line for print 58 ** statement */ 59 @wideline char(84), /* special print line when lots of % are used. 60 ** make sure you don't line wrap */ 61 @section char(80), /* string to delimit sections on printout */ 62 @summary_line char(80) 63 64 /* --------- Setup Environment --------- */ 65 set nocount on /* disable row counts being sent to client */ 66 67 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 68 select @sum2line = " ------------------------- ------------ ------------ ----------" 69 select @blankline = " " 70 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 71 select @na_str = "n/a" 72 select @section = "===============================================================================" 73 74 75 /* ======================= Kernel Utilization Section =================== */ 76 print @section 77 print @blankline 78 print "Kernel Utilization" 79 print "------------------" 80 print @blankline 81 82 select @cpu_busy_sum = 0, @cpu_busy_avg = 0, 83 @cpu_busy_server = 0, @cpu_server_avg = 0, 84 @user_busy_sum = 0, @user_busy_avg = 0, 85 @system_busy_sum = 0, @system_busy_avg = 0, 86 @user_busy_server = 0, @system_busy_server = 0, 87 @thr_count = 0, @tmp_tot = 0, 88 @tmp_server = 0 89 90 select ThreadPoolID, ThreadPoolName, Size, Type 91 into #tmpThreadPool 92 from master.dbo.monThreadPool order by ThreadPoolName 93 94 select StatisticID, l.EngineNumber, Avg_1min, Avg_5min, 95 Avg_15min, ThreadPoolID 96 into #tmpLoad 97 from master.dbo.monSysLoad l, 98 master.dbo.monEngine e, 99 master.dbo.monThread t 100 where l.StatisticID in (4, 5) 101 and l.EngineNumber = e.EngineNumber 102 and e.ThreadID = t.ThreadID 103 104 /* Common Cursors */ 105 declare tpcursor cursor for 106 select ThreadPoolID, ThreadPoolName, Size 107 from #tmpThreadPool 108 order by ThreadPoolName 109 110 declare epcursor cursor for 111 select ThreadPoolID, ThreadPoolName, Size 112 from #tmpThreadPool 113 where Type = "Engine (Multiplexed)" 114 order by ThreadPoolName 115 116 declare engcursor cursor for 117 select engineid, enginename 118 from #muxthreadsinfo 119 where tpname = @TpName 120 order by engineid 121 122 /************************************************* 123 ** Engine Utilization ** 124 *************************************************/ 125 126 print @blankline 127 print " Engine Utilization (Tick %%) User Busy System Busy I/O Busy Idle" 128 print @sum1line 129 130 /* build a temp table that has the usage info for each engine */ 131 select isnull(100.0 * convert(real, u.value) / t.value, 0) "UserBusy", 132 isnull(100.0 * convert(real, s.value) / t.value, 0) "SystemBusy", 133 isnull(100.0 * convert(real, io.value) / t.value, 0) "IOBusy", 134 isnull(100.0 * convert(real, i.value) / t.value, 0) "Idle", 135 t.group_name 136 into #tmpEngUtilization 137 from #tempmonitors u, #tempmonitors s, 138 #tempmonitors io, #tempmonitors i, 139 #tempmonitors t 140 where u.group_name = t.group_name 141 and s.group_name = t.group_name 142 and io.group_name = t.group_name 143 and i.group_name = t.group_name 144 and u.field_name = "user_ticks" 145 and s.field_name = "system_ticks" 146 and io.field_name = "io_ticks" 147 and i.field_name = "idle_ticks" 148 and t.field_name = "clock_ticks" 149 and t.value > 0 150 151 open epcursor 152 fetch epcursor into @TpId, @TpName, @thr_count 153 while (@@sqlstatus = 0) 154 begin 155 select @rptline = " ThreadPool : " + @TpName 156 print @rptline 157 158 open engcursor 159 fetch engcursor into @EngineId, @tmp_grp 160 while (@@sqlstatus = 0) 161 begin 162 select @wideline = " Engine " + convert(char(4), @EngineId) 163 + space(20) 164 + str(UserBusy, 5, 1) + @psign + space(7) 165 + str(SystemBusy, 5, 1) + @psign + space(5) 166 + str(IOBusy, 5, 1) + @psign + space(5) 167 + str(Idle, 5, 1) + @psign 168 from #tmpEngUtilization 169 where group_name = @tmp_grp 170 print @wideline 171 172 fetch engcursor into @EngineId, @tmp_grp 173 end 174 close engcursor 175 176 /* Print the Average and Summary of each threadpool */ 177 if @thr_count > 1 178 begin 179 print @sum1line 180 select @wideline = " Pool Summary " + space(7) + "Total" 181 + space(5) 182 + str(sum(UserBusy), 7, 1) + @psign + space(5) 183 + str(sum(SystemBusy), 7, 1) + @psign + space(3) 184 + str(sum(IOBusy), 7, 1) + @psign + space(3) 185 + str(sum(Idle), 7, 1) + @psign 186 from #tmpEngUtilization 187 where group_name in 188 (select enginename from #muxthreadsinfo 189 where tpname = @TpName) 190 print @wideline 191 select @wideline = space(20) + "Average" 192 + space(7) 193 + str(avg(UserBusy), 5, 1) + @psign + space(7) 194 + str(avg(SystemBusy), 5, 1) + @psign + space(5) 195 + str(avg(IOBusy), 5, 1) + @psign + space(5) 196 + str(avg(Idle), 5, 1) + @psign 197 from #tmpEngUtilization 198 where group_name in 199 (select enginename from #muxthreadsinfo 200 where tpname = @TpName) 201 print @wideline 202 end 203 print @blankline 204 205 fetch epcursor into @TpId, @TpName, @thr_count 206 end /* loop of pools */ 207 close epcursor 208 209 210 /* Print the Server Summary */ 211 if @NumMuxThreads > 1 212 begin 213 print @sum1line 214 select @wideline = " Server Summary " + space(5) 215 + "Total" + space(5) 216 + str(sum(UserBusy), 7, 1) + @psign + space(5) 217 + str(sum(SystemBusy), 7, 1) + @psign + space(3) 218 + str(sum(IOBusy), 7, 1) + @psign + space(3) 219 + str(sum(Idle), 7, 1) + @psign 220 from #tmpEngUtilization 221 print @wideline 222 select @wideline = space(20) + "Average" + space(7) 223 + str(avg(UserBusy), 5, 1) + @psign + space(7) 224 + str(avg(SystemBusy), 5, 1) + @psign + space(5) 225 + str(avg(IOBusy), 5, 1) + @psign + space(5) 226 + str(avg(Idle), 5, 1) + @psign 227 from #tmpEngUtilization 228 print @wideline 229 end /* Server Summary */ 230 print @blankline 231 232 233 /************************************************* 234 ** Run Queue Length ** 235 *************************************************/ 236 237 print @blankline 238 print " Average Runnable Tasks 1 min 5 min 15 min %% of total" 239 print @sum1line 240 241 declare loadcursor cursor for 242 select EngineNumber, StatisticID, Avg_1min, Avg_5min, Avg_15min 243 from #tmpLoad 244 where ThreadPoolID = @TpId 245 order by StatisticID desc, 246 EngineNumber 247 248 open epcursor 249 fetch epcursor into @TpId, @TpName, @thr_count 250 while (@@sqlstatus = 0) 251 begin 252 select @real_tot = sum(Avg_1min) from 253 #tmpLoad where ThreadPoolID = @TpId 254 255 select @rptline = " ThreadPool : " + @TpName 256 print @rptline 257 258 open loadcursor 259 fetch loadcursor into @EngineId, @tmp_int, @tmp_real1, 260 @tmp_real2, @tmp_real3 261 while (@@sqlstatus = 0) 262 begin 263 select @rptline = (case when @tmp_int = 4 264 then " Engine " 265 + convert(char(4), @EngineId) 266 + space(22) 267 else " Global Queue" + space(21) 268 end) 269 + str(@tmp_real1, 5, 1) + space(9) 270 + str(@tmp_real2, 5, 1) + space(7) 271 + str(@tmp_real3, 5, 1) + space(5) 272 + (case when @real_tot = 0 273 then str(0, 5, 1) 274 else str(100.0 * @tmp_real1 / @real_tot, 5, 1) 275 end) 276 + @psign 277 print @rptline 278 279 fetch loadcursor into @EngineId, @tmp_int, @tmp_real1, 280 @tmp_real2, @tmp_real3 281 end 282 close loadcursor 283 284 if (@thr_count > 1) 285 begin 286 select @summary_line = " Pool Summary" + space(8) 287 + "Total" + space(7) 288 + str(sum(Avg_1min), 7, 1) + space(7) 289 + str(sum(Avg_5min), 7, 1) + space(5) 290 + str(sum(Avg_15min), 7, 1) 291 from #tmpLoad where ThreadPoolID = @TpId 292 print @sum2line 293 print @summary_line 294 select @summary_line = space(20) 295 + "Average" + space(7) 296 + str(avg(Avg_1min), 7, 1) + space(7) 297 + str(avg(Avg_5min), 7, 1) + space(5) 298 + str(avg(Avg_15min), 7, 1) 299 from #tmpLoad where ThreadPoolID = @TpId 300 print @summary_line 301 end 302 303 print @blankline 304 305 fetch epcursor into @TpId, @TpName, @thr_count 306 end /* loop of pools */ 307 308 print @sum2line 309 select @summary_line = " Server Summary" + space(6) 310 + "Total" + space(7) 311 + str(sum(Avg_1min), 7, 1) + space(7) 312 + str(sum(Avg_5min), 7, 1) + space(5) 313 + str(sum(Avg_15min), 7, 1) 314 from #tmpLoad 315 print @summary_line 316 select @summary_line = space(20) 317 + "Average" + space(7) 318 + str(avg(Avg_1min), 7, 1) + space(7) 319 + str(avg(Avg_5min), 7, 1) + space(5) 320 + str(avg(Avg_15min), 7, 1) 321 from #tmpLoad 322 print @summary_line 323 print @blankline 324 325 close epcursor 326 deallocate loadcursor 327 328 329 /************************************************* 330 ** Engine Sleeps ** 331 *************************************************/ 332 333 print @blankline 334 print " CPU Yields by Engine per sec per xact count %% of total" 335 print @sum1line 336 337 select @tmp_total = SUM(value) 338 from #tempmonitors 339 where group_name like "engine_%" and 340 field_name = "engine_sleeps" 341 342 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 343 begin 344 select @rptline = " Total CPU Yields 0.0 0.0 0 n/a" 345 print @rptline 346 end 347 else 348 begin 349 open epcursor 350 fetch epcursor into @TpId, @TpName, @thr_count 351 while (@@sqlstatus = 0) 352 begin 353 select @rptline = " ThreadPool : " + @TpName 354 print @rptline 355 356 select @tmp_tot = SUM(value) from #tempmonitors where group_name in 357 (select enginename from #muxthreadsinfo where 358 tpname = @TpName) and field_name = "engine_sleeps" 359 360 open engcursor 361 fetch engcursor into @EngineId, @tmp_grp 362 while (@@sqlstatus = 0) 363 begin 364 select @tmp_int = value from #tempmonitors 365 where group_name = @tmp_grp and 366 field_name = "engine_sleeps" 367 368 select @tmp_int2 = value from #tempmonitors 369 where group_name = @tmp_grp and 370 field_name = "engine_sleep_interrupted" 371 372 /* 373 ** Make tmp_int the number of full sleeps. Due to 374 ** timing issues collecting the monitor counters we may 375 ** end up with more interrupted sleeps than total 376 ** sleeps. If this is the case we just consider full 377 ** sleeps to be zero. 378 */ 379 select @tmp_int = 380 case when @tmp_int > @tmp_int2 381 then @tmp_int - @tmp_int2 382 else 0 383 end 384 385 if @tmp_tot != 0 386 begin 387 select @rptline = " Engine " + convert(char(4), @EngineId) 388 print @rptline 389 390 select @rptline = " Full Sleeps" 391 + space(12) 392 + str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 393 + space(2) + 394 str(@tmp_int / convert(real, @NumXacts), 12, 1) 395 + space(2) + 396 str(@tmp_int, 10) + space(5) + 397 str(100.0 * @tmp_int / @tmp_tot, 5, 1) 398 + @psign 399 print @rptline 400 401 select @rptline = " Interrupted Sleeps" 402 + space(5) 403 + str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) 404 + space(2) + 405 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) 406 + space(2) + 407 str(@tmp_int2, 10) + space(5) + 408 str(100.0 * @tmp_int2 / @tmp_tot, 5, 1) 409 + @psign 410 print @rptline 411 end 412 else 413 begin 414 select @rptline = " Engine " + convert(char(4), @EngineId) + 415 space(24) + 416 "0.0 0.0 0 n/a" 417 print @rptline 418 end 419 420 fetch engcursor into @EngineId, @tmp_grp 421 end 422 close engcursor 423 424 /* Print the Pool Average */ 425 if @thr_count > 1 426 begin 427 print @sum2line 428 select @rptline = " Pool Summary " + space(14) + 429 str(@tmp_tot / (@NumElapsedMs / 1000.0), 12, 1) 430 + space(2) + 431 str(@tmp_tot / convert(real, @NumXacts), 12, 1) 432 + space(2) + 433 str(@tmp_tot, 10) 434 print @rptline 435 end 436 437 print @blankline 438 439 fetch epcursor into @TpId, @TpName, @thr_count 440 end /* loop of pools */ 441 442 close epcursor 443 444 /* Print the Server Summary */ 445 if @NumMuxThreads > 1 446 begin 447 print @sum2line 448 select @rptline = " Total CPU Yields " + space(10) + 449 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 450 + space(2) + 451 str(@tmp_total / convert(real, @NumXacts), 12, 1) 452 + space(2) + 453 str(@tmp_total, 10) 454 print @rptline 455 end 456 457 end 458 print @blankline 459 460 461 /************************************************* 462 ** Thread Utilization ** 463 *************************************************/ 464 set @tmp_tot = 0 465 466 print @blankline 467 print " Thread Utilization (OS %%) User Busy System Busy Idle" 468 print @sum2line 469 470 471 declare threadcursor cursor for 472 select ThreadID, UserTime, SystemTime, TaskName 473 from tempdb.dbo.tempThreadStats 474 where ThreadPoolID = @TpId 475 order by ThreadID 476 477 open tpcursor 478 fetch tpcursor into @TpId, @TpName, @thr_count 479 while (@@sqlstatus = 0) 480 begin 481 /* use @tmp_int to track the number of thread rows printed in a pool */ 482 select @tmp_int = 0 483 484 open threadcursor 485 fetch threadcursor into @ThreadId, @tmp_bigint1, @tmp_bigint2, @TaskName 486 while (@@sqlstatus = 0) 487 begin 488 select @user_busy = 100.0 * @tmp_bigint1 / @NumElapsedMs 489 select @user_busy_sum = @user_busy_sum + @user_busy 490 select @system_busy = 100.0 * @tmp_bigint2 / @NumElapsedMs 491 select @system_busy_sum = @system_busy_sum + @system_busy 492 select @idle = 100 - (@user_busy + @system_busy) 493 494 if (@TaskName like "Engine%" or 495 @tmp_bigint1 > 0 or 496 @tmp_bigint2 > 0) 497 begin 498 499 if (@tmp_int = 0) 500 begin 501 /* print the hadter the first time through */ 502 select @rptline = " ThreadPool : " + @TpName 503 print @rptline 504 end 505 506 select @rptline = " Thread " + convert(char(4), @ThreadId) 507 + convert(char(18), (" (" + @TaskName + ")")) 508 + space(2) 509 + str(@user_busy, 5, 1) + @psign + space(7) 510 + str(@system_busy, 5, 1) + @psign + space(5) 511 + (case when @idle < 0 512 then str(0, 5, 1) 513 else str(@idle, 5, 1) 514 end) 515 + @psign 516 print @rptline 517 select @tmp_int = @tmp_int + 1 518 end 519 520 fetch threadcursor into @ThreadId, @tmp_bigint1, 521 @tmp_bigint2, @TaskName 522 end /* loop of threads */ 523 close threadcursor 524 525 set @user_busy_server = @user_busy_server + @user_busy_sum 526 set @system_busy_server = @system_busy_server + @system_busy_sum 527 set @tmp_tot = @tmp_tot + @thr_count 528 529 if @tmp_int = 0 530 begin 531 select @rptline = " ThreadPool : " + @TpName + " : no activity during sample" 532 print @rptline 533 end 534 else 535 if @thr_count > 1 536 begin 537 select @idle = @thr_count * 100 - (@user_busy_sum + @system_busy_sum) 538 select @summary_line = " Pool Summary" + space(6) 539 + "Total" + space(7) 540 + str(@user_busy_sum, 7, 1) + @psign + space(5) 541 + str(@system_busy_sum, 7, 1) + @psign + space(3) 542 + (case when @idle < 0 543 then str(0, 7, 1) 544 else str(@idle, 7, 1) 545 end) 546 + @psign, 547 @user_busy_avg = @user_busy_sum / @thr_count, 548 @system_busy_avg = @system_busy_sum / @thr_count 549 select @idle = 100 - (@user_busy_avg + @system_busy_avg) 550 select @rptline = space(18) + "Average" + space(9) 551 + str(@user_busy_avg, 5, 1) + @psign + space(7) 552 + str(@system_busy_avg, 5, 1) + @psign + space(5) 553 + (case when @idle < 0 554 then str(0, 5, 1) 555 else str(@idle, 5, 1) 556 end) 557 + @psign 558 print @sum2line 559 print @summary_line 560 print @rptline 561 end 562 print @blankline 563 564 /* reset the counters */ 565 select @user_busy_sum = 0, @user_busy_avg = 0, 566 @system_busy_sum = 0, @system_busy_avg = 0 567 568 fetch tpcursor into @TpId, @TpName, @thr_count 569 end /* loop of pools */ 570 571 select @idle = @tmp_tot * 100 - (@user_busy_server + @system_busy_server) 572 select @summary_line = " Server Summary " + space(3) + "Total" + space(7) 573 + str(@user_busy_server, 7, 1) + @psign + space(5) 574 + str(@system_busy_server, 7, 1) + @psign + space(3) 575 + (case when @idle < 0 576 then str(0, 7, 1) 577 else str(@idle, 7, 1) 578 end) 579 + @psign, 580 @user_busy_avg = @user_busy_server / @tmp_tot, 581 @system_busy_avg = @system_busy_server / @tmp_tot 582 select @idle = 100 - (@user_busy_avg + @system_busy_avg) 583 select @rptline = space(18) + "Average" + space(9) 584 + str(@user_busy_avg, 5, 1) + @psign + space(7) 585 + str(@system_busy_avg, 5, 1) + @psign + space(5) 586 + (case when @idle < 0 587 then str(0, 5, 1) 588 else str(@idle, 5, 1) 589 end) 590 + @psign 591 print @sum2line 592 print @summary_line 593 print @rptline 594 595 print @blankline 596 select @real_tot = (sum(UserTime) + sum(SystemTime)) / (1.0 * @NumElapsedMs) 597 from tempdb.dbo.tempThreadStats 598 select @rptline = " Adaptive Server threads are consuming " 599 + ltrim(str(@real_tot, 5, 1)) 600 + " CPU units." 601 print @rptline 602 select @tmp_real1 = @NumXacts * 1000.0 / @NumElapsedMs 603 604 if (@real_tot != 0) 605 begin 606 select @rptline = " Throughput (committed xacts per CPU unit) : " 607 + ltrim(str(@tmp_real1 / @real_tot, 12, 1)) 608 end 609 else 610 begin 611 select @rptline = " Throughput (committed xacts per CPU unit) : n/a " 612 end 613 print @rptline 614 print @blankline 615 616 close tpcursor 617 deallocate threadcursor 618 619 /************************************************* 620 ** Page Faults and Context Switches ** 621 *************************************************/ 622 623 select @tmp_bigint1 = sum(MinorFaults), 624 @tmp_bigint2 = sum(MajorFaults) 625 from tempdb.dbo.tempThreadStats 626 set @tmp_total = @tmp_bigint1 + @tmp_bigint2 627 628 if (@tmp_total > 0) 629 begin 630 print @blankline 631 print " Page Faults at OS per sec per xact count %% of total" 632 print @sum1line 633 634 select @rptline = " Minor Faults" 635 + space(14) 636 + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1) 637 + space(2) 638 + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1) 639 + space(2) 640 + str(@tmp_bigint1, 10) + space(5) 641 + str(100.0 * @tmp_bigint1 / @tmp_total, 5, 1) + @psign 642 print @rptline 643 644 select @rptline = " Major Faults" 645 + space(14) 646 + str(@tmp_bigint2 / (@NumElapsedMs / 1000.0), 12, 1) 647 + space(2) 648 + str(@tmp_bigint2 / convert(real, @NumXacts), 12, 1) 649 + space(2) 650 + str(@tmp_bigint2, 10) + space(5) 651 + str(100.0 * @tmp_bigint2 / @tmp_total, 5, 1) + @psign 652 print @rptline 653 654 print @sum1line 655 select @rptline = " Total Page Faults" 656 + space(9) 657 + str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 658 + space(2) 659 + str(@tmp_total / convert(real, @NumXacts), 12, 1) 660 + space(2) 661 + str(@tmp_total, 10) 662 + space(5) + "100.0" + @psign 663 print @rptline 664 665 print @blankline 666 end 667 668 select @tmp_total = sum(VoluntaryCtxtSwitches) + sum(NonVoluntaryCtxtSwitches) 669 from tempdb.dbo.tempThreadStats 670 671 if (@tmp_total > 0) 672 begin 673 print @blankline 674 print " Context Switches at OS per sec per xact count %% of total" 675 print @sum1line 676 677 open tpcursor 678 fetch tpcursor into @TpId, @TpName, @thr_count 679 while (@@sqlstatus = 0) 680 begin 681 select @tmp_bigint1 = sum(VoluntaryCtxtSwitches), 682 @tmp_bigint2 = sum(NonVoluntaryCtxtSwitches) 683 from tempdb.dbo.tempThreadStats 684 where ThreadPoolID = @TpId 685 686 select @rptline = " ThreadPool : " + @TpName 687 print @rptline 688 689 select @rptline = " Voluntary" 690 + space(17) 691 + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1) 692 + space(2) 693 + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1) 694 + space(2) 695 + str(@tmp_bigint1, 10) + space(5) 696 + str(100.0 * @tmp_bigint1 / @tmp_total, 5, 1) + @psign 697 print @rptline 698 699 select @rptline = " Non-Voluntary" 700 + space(13) 701 + str(@tmp_bigint2 / (@NumElapsedMs / 1000.0), 12, 1) 702 + space(2) 703 + str(@tmp_bigint2 / convert(real, @NumXacts), 12, 1) 704 + space(2) 705 + str(@tmp_bigint2, 10) + space(5) 706 + str(100.0 * @tmp_bigint2 / @tmp_total, 5, 1) + @psign 707 print @rptline 708 709 fetch tpcursor into @TpId, @TpName, @thr_count 710 end 711 close tpcursor 712 713 print @sum1line 714 select @rptline = " Total Context Switches" 715 + space(4) 716 + str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 717 + space(2) 718 + str(@tmp_total / convert(real, @NumXacts), 12, 1) 719 + space(2) 720 + str(@tmp_total, 10) 721 + space(5) + "100.0" + @psign 722 print @rptline 723 724 print @blankline 725 end 726 727 728 /************************************************* 729 ** IO Controllers ** 730 *************************************************/ 731 declare iocursor cursor for 732 select distinct Type from tempdb.dbo.tempIOCStats 733 order by Type 734 735 open iocursor 736 fetch iocursor into @TaskName 737 while (@@sqlstatus = 0) 738 begin 739 select @rptline = space(2) + convert(char(32), @TaskName + " Activity") 740 + "per sec per xact count %% of total" 741 print @blankline 742 print @rptline 743 print @sum1line 744 745 select @tmp_total = sum(BlockingPolls) + sum(NonBlockingPolls), 746 @tmp_bigint1 = sum(EventPolls), 747 @tmp_bigint2 = sum(FullPolls) 748 from tempdb.dbo.tempIOCStats 749 where Type = @TaskName 750 751 if @tmp_total = 0 752 begin 753 select @rptline = " Polls" 754 + space(29) + "0.0" 755 + space(11) + "0.0" 756 + space(11) + "0" 757 + space(7) + "0.0" + @psign 758 print @rptline 759 760 fetch iocursor into @TaskName 761 continue 762 end 763 764 select @rptline = " Polls" 765 + space(21) 766 + str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 767 + space(2) 768 + str(@tmp_total / convert(real, @NumXacts), 12, 1) 769 + space(2) 770 + str(@tmp_total, 10) + space(7) + @na_str 771 print @rptline 772 773 if @tmp_bigint1 = 0 774 begin 775 select @rptline = " Polls Returning Events" 776 + space(13) + "0.0" 777 + space(11) + "0.0" 778 + space(11) + "0" 779 + space(7) + "0.0" + @psign 780 print @rptline 781 782 fetch iocursor into @TaskName 783 continue 784 end 785 786 select @rptline = " Polls Returning Events" 787 + space(4) 788 + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1) 789 + space(2) 790 + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1) 791 + space(2) 792 + str(@tmp_bigint1, 10) + space(5) 793 + str(100.0 * @tmp_bigint1 / @tmp_total, 5, 1) + @psign 794 print @rptline 795 796 select @rptline = " Polls Returning Max Events" 797 + str(@tmp_bigint2 / (@NumElapsedMs / 1000.0), 12, 1) 798 + space(2) 799 + str(@tmp_bigint2 / convert(real, @NumXacts), 12, 1) 800 + space(2) 801 + str(@tmp_bigint2, 10) + space(5) 802 + str(100.0 * @tmp_bigint2 / @tmp_total, 5, 1) + @psign 803 print @rptline 804 805 806 select @tmp_bigint1 = sum(Events) 807 from tempdb.dbo.tempIOCStats 808 where Type = @TaskName 809 810 select @rptline = " Total Events" 811 + space(14) 812 + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1) 813 + space(2) 814 + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1) 815 + space(2) 816 + str(@tmp_bigint1, 10) 817 + space(7) + @na_str 818 print @rptline 819 820 select @rptline = " Events Per Poll" 821 + space(20) 822 + @na_str + space(11) 823 + @na_str + space(5) 824 + str((convert(real, sum(Events)) / @tmp_total), 7, 3) 825 + space(7) + @na_str 826 from tempdb.dbo.tempIOCStats 827 where Type = @TaskName 828 print @rptline 829 830 fetch iocursor into @TaskName 831 end 832 print @blankline 833 close iocursor 834 deallocate iocursor 835 836 /************************************************* 837 ** Blocking Calls ** 838 *************************************************/ 839 print @blankline 840 print " Blocking Call Activity per sec per xact count %% of total" 841 print @sum1line 842 843 select @tmp_tot = TotalRequests, 844 @tmp_int = QueuedRequests, 845 @tmp_int2 = WaitTime 846 from tempdb.dbo.tempWorkQueue 847 where Name = "syb_blocking_pool" 848 849 if @tmp_tot = 0 850 begin 851 select @rptline = " Total Requests 0.0 0.0 0 n/a" 852 print @rptline 853 end 854 else 855 begin 856 select @rptline = " Serviced Requests" 857 + space(9) 858 + str((@tmp_tot - @tmp_int) / (@NumElapsedMs / 1000.0), 12, 1) 859 + space(2) + 860 str((@tmp_tot - @tmp_int) / convert(real, @NumXacts), 12, 1) 861 + space(2) + 862 str((@tmp_tot - @tmp_int), 10) + space(5) + 863 str(100.0 * (@tmp_tot - @tmp_int) / @tmp_tot, 5, 1) 864 + @psign 865 print @rptline 866 867 select @rptline = " Queued Requests" 868 + space(11) 869 + str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 870 + space(2) + 871 str(@tmp_int / convert(real, @NumXacts), 12, 1) 872 + space(2) + 873 str(@tmp_int, 10) + space(5) + 874 str(100.0 * @tmp_int / @tmp_tot, 5, 1) 875 + @psign 876 print @rptline 877 878 print @sum2line 879 880 select @rptline = " Total Requests" 881 + space(13) + 882 str(@tmp_tot / (@NumElapsedMs / 1000.0), 12, 1) 883 + space(2) + 884 str(@tmp_tot / convert(real, @NumXacts), 12, 1) 885 + space(2) + 886 str(@tmp_tot, 10) 887 print @rptline 888 889 select @rptline = " Total Wait Time (ms)" 890 + space(16) 891 + @na_str + space(11) 892 + @na_str + space(2) 893 + str(@tmp_int2, 10) 894 print @rptline 895 end 896 print @blankline 897 898 /* cleanup common cursors */ 899 deallocate tpcursor 900 deallocate engcursor 901 deallocate epcursor 902 903 return 0 904 905
exec sp_procxmode 'sp_sysmon_kernel_threaded', 'AnyMode' go Grant Execute on sp_sysmon_kernel_threaded to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..monSysLoad (1)  read_writes table tempdb..#tmpThreadPool (1) reads table master..monEngine (1) read_writes table tempdb..#tmpEngUtilization (1) read_writes table tempdb..#tmpLoad (1) reads table tempdb..#muxthreadsinfo (1) reads table master..monThread (1) reads table tempdb..#tempmonitors (1) reads table master..monThreadPool (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
