Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_locks ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** of locking characteristics including lock requests and waits. 4 */ 5 6 create procedure sp_sysmon_locks 7 8 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 9 @NumXacts int, /* for per transactions calculations */ 10 @Reco char(1), 11 @NumEngines tinyint = 1 /* number of engines online */ 12 as 13 14 /* --------- declare local variables --------- */ 15 16 declare @TotalLocks int /* Count Total Number of Locks */ 17 declare @tmp_int int /* temp var for integer storage */ 18 declare @tmp_int2 int /* temp var for integer storage */ 19 declare @tmp_access int /* temp var for integer storage */ 20 declare @tmp_xls int /* temp var for integer storage */ 21 declare @tmp_total int /* temp var for summing 'total #s' data */ 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 25 ** percent 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 32 /* ------------- Variables for Suggested Recomendations ------------*/ 33 declare @recotxt char(80) 34 declare @recoline char(80) 35 declare @maxengfreelocks int /* Value for max engine freelocks */ 36 declare @max_online_engine int /* Max online engine count */ 37 declare @freelock_tr_blksize char(5) /* Freelock transfer block size */ 38 declare @deadlocks int /* Deadlock count */ 39 declare @deadlock_search int /* Deadlock Search count */ 40 declare @tempmaxengfreelocks char(5) /* Temp maxengfreelocks for convert */ 41 declare @curfreelocks int 42 declare @tot_movefreelock_toengine int 43 declare @tot_movefreelock_toserver int 44 declare @newval char(5) 45 declare @tmp_num_lock_op int 46 declare @reco_hdr_prn bit 47 declare @reco_lastpg_wait_percent real 48 declare @reco_avg_chain_length_pagerow real 49 declare @spinlock_contention float 50 declare @reco_tabspin_contention float 51 declare @reco_addrspin_contention float 52 declare @reco_fgspin_contention float 53 declare @totalrequested int 54 declare @totalretained int 55 declare @totaldiskreads int 56 declare @totaldeadlocks int 57 declare @totalwaits int 58 declare @totaltransfers int 59 declare @totallockmgrcalls int 60 declare @NumElapsedSec real 61 declare @totalreqs int 62 declare @totallocalmaster int 63 declare @totalgrants int 64 declare @totalbastreqs int 65 66 67 /* --------- Setup Environment --------- */ 68 set nocount on /* disable row counts being sent to client */ 69 70 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 71 select @sum2line = " ------------------------- ------------ ------------ ----------" 72 select @blankline = " " 73 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 74 select @na_str = "n/a" 75 select @recotxt = " Tuning Recommendations for Lock Management" 76 select @recoline = " ------------------------------------------" 77 select @section = "===============================================================================" 78 79 print @section 80 print @blankline 81 82 print "Lock Management" 83 print "---------------" 84 print @blankline 85 print " Lock Summary per sec per xact count %% of total" 86 print @sum1line 87 88 select @NumElapsedSec = @NumElapsedMs / 1000.0 89 90 select @TotalLocks = SUM(value) 91 from #tempmonitors 92 where group_name = "lock" and 93 (field_name like "granted%" or field_name like "waited%") 94 95 select @totalretained = SUM(value) 96 from #tempmonitors 97 where group_name = "lock" and 98 field_name like "clm_logical_retained" 99 100 101 select @rptline = " Total Lock Requests" + space(8) + 102 str(@TotalLocks / (@NumElapsedMs / 1000.0), 12, 1) + 103 space(2) + 104 str(@TotalLocks / convert(real, @NumXacts), 12, 1) + 105 space(2) + 106 str(@TotalLocks, 10) + space(7) + 107 @na_str 108 print @rptline 109 110 if @TotalLocks != 0 /* Avoid Divide by Zero Errors after printout */ 111 begin 112 select @tmp_int = SUM(value) 113 from #tempmonitors 114 where group_name = "lock" and 115 field_name like "%waited%" 116 117 select @rptline = " Avg Lock Contention" + space(8) + 118 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 119 space(2) + 120 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 121 space(2) + 122 str(@tmp_int, 10) + space(5) + 123 str(100.0 * @tmp_int / @TotalLocks, 5, 1) + 124 @psign 125 print @rptline 126 select @rptline = " Cluster Locks Retained" + space(5) + 127 str(@totalretained / (@NumElapsedMs / 1000.0), 12, 1) + 128 space(2) + 129 str(@totalretained / convert(real, @NumXacts), 12, 1) + 130 space(2) + 131 str(@totalretained, 10) + space(5) + 132 str(100.0 * @totalretained / @TotalLocks, 5, 1) + 133 @psign 134 print @rptline 135 136 select @tmp_int = SUM(value) 137 from #tempmonitors 138 where group_name = "lock" and 139 field_name = "deadlocks" 140 141 select @rptline = " Deadlock Percentage" + space(8) + 142 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 143 space(2) + 144 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 145 space(2) + 146 str(@tmp_int, 10) + space(5) + 147 str(100.0 * @tmp_int / @TotalLocks, 5, 1) + 148 @psign 149 print @rptline 150 151 print @blankline 152 153 /* 154 ** Get spinlock related counters for hashtable spinlock contentions 155 */ 156 select P.field_name as name, 157 P.value as grabs, 158 W.value as waits, 159 S.value as spins 160 into #foo 161 from #tempmonitors P, #tempmonitors W, #tempmonitors S 162 where 163 P.group_name = "spinlock_p" 164 and W.group_name = "spinlock_w" 165 and S.group_name = "spinlock_s" 166 and P.field_id = W.field_id 167 and P.field_id = S.field_id 168 and P.field_name in ('fglockspins', 'tablockspins', 'addrlockspins') 169 170 print " Lock Detail per sec per xact count %% of total" 171 print @sum1line 172 print @blankline 173 174 print " Table Lock Hashtable" 175 select @tmp_int = value 176 from #tempmonitors 177 where group_name = "lock" and 178 field_name = "hashtab_lookups_table_lock" 179 180 select @rptline = " Lookups" + space(18) + 181 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 182 space(2) + 183 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 184 space(2) + 185 str(@tmp_int, 10) + space(7) + 186 @na_str 187 print @rptline 188 189 /* Avoid divide by 0 error */ 190 if (@tmp_int > 0) 191 begin 192 select @tmp_int2 = value 193 from #tempmonitors 194 where group_name = "lock" and 195 field_name = "hashtab_overflow_table_lock" 196 197 select @rptline = " Avg Chain Length" + space(18) + 198 @na_str + space(11) + 199 @na_str + space(2) + 200 str(convert(real, @tmp_int2) / @tmp_int, 10, 5) + 201 space(7) + @na_str 202 print @rptline 203 end 204 205 select @spinlock_contention = isnull(100.0 * (sum(waits) / sum(grabs)), 0) 206 from #foo 207 where name = 'tablockspins' 208 and grabs > 0 209 210 select @rptline = " Spinlock Contention" + space(15) + 211 @na_str + space(11) + 212 @na_str + space(9) + 213 @na_str + space(5) + 214 str(@spinlock_contention, 5, 1) 215 + @psign 216 , @reco_tabspin_contention = @spinlock_contention 217 print @rptline 218 print @blankline 219 220 print " Exclusive Table" 221 222 select @tmp_total = SUM(value) 223 from #tempmonitors 224 where group_name = "lock" and 225 (field_name like "granted%_EX_TAB" or 226 field_name like "waited%_EX_TAB") 227 228 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 229 begin 230 select @rptline = " Total EX-Table Requests 0.0 0.0 0 n/a" 231 print @rptline 232 end 233 else 234 begin 235 select @tmp_int = sum(value) 236 from #tempmonitors 237 where group_name = "lock" and 238 field_name like "granted%_EX_TAB" 239 240 select @rptline = " Granted" + space(18) + 241 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 242 space(2) + 243 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 244 space(2) + 245 str(@tmp_int, 10) + space(5) + 246 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 247 @psign 248 print @rptline 249 250 select @tmp_int = sum(value) 251 from #tempmonitors 252 where group_name = "lock" and 253 field_name like "waited%_EX_TAB" 254 255 select @rptline = " Waited" + space(19) + 256 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 257 space(2) + 258 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 259 space(2) + 260 str(@tmp_int, 10) + space(5) + 261 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 262 @psign 263 print @rptline 264 print @sum1line 265 select @rptline = " Total EX-Table Requests" + space(4) + 266 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 267 space(2) + 268 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 269 space(2) + 270 str(@tmp_total, 10) + space(5) + 271 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + 272 @psign 273 print @rptline 274 end 275 print @blankline 276 277 print " Shared Table" 278 279 select @tmp_total = SUM(value) 280 from #tempmonitors 281 where group_name = "lock" and 282 (field_name like "granted%_SH_TAB" or field_name like "waited%_SH_TAB") 283 284 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 285 begin 286 select @rptline = " Total SH-Table Requests 0.0 0.0 0 n/a" 287 print @rptline 288 end 289 else 290 begin 291 select @tmp_int = sum(value) 292 from #tempmonitors 293 where group_name = "lock" and 294 field_name like "granted%_SH_TAB" 295 296 select @rptline = " Granted" + space(18) + 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) + 303 @psign 304 print @rptline 305 306 select @tmp_int = sum(value) 307 from #tempmonitors 308 where group_name = "lock" and 309 field_name like "waited%_SH_TAB" 310 311 select @rptline = " Waited" + space(19) + 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 / @tmp_total, 5, 1) + 318 @psign 319 print @rptline 320 321 print @sum1line 322 select @rptline = " Total SH-Table Requests" + space(4) + 323 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 324 space(2) + 325 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 326 space(2) + 327 str(@tmp_total, 10) + space(5) + 328 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + 329 @psign 330 print @rptline 331 end 332 print @blankline 333 334 print " Exclusive Intent" 335 336 select @tmp_total = SUM(value) 337 from #tempmonitors 338 where group_name = "lock" and 339 (field_name like "granted%_EX_INT" or 340 field_name like "waited%_EX_INT") 341 342 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 343 begin 344 select @rptline = " Total EX-Intent Requests 0.0 0.0 0 n/a" 345 print @rptline 346 end 347 else 348 begin 349 select @tmp_int = sum(value) 350 from #tempmonitors 351 where group_name = "lock" and 352 field_name like "granted%_EX_INT" 353 354 select @rptline = " Granted" + space(18) + 355 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 356 space(2) + 357 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 358 space(2) + 359 str(@tmp_int, 10) + space(5) + 360 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 361 @psign 362 print @rptline 363 364 select @tmp_int = sum(value) 365 from #tempmonitors 366 where group_name = "lock" and 367 field_name like "waited%_EX_INT" 368 369 select @rptline = " Waited" + space(19) + 370 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 371 space(2) + 372 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 373 space(2) + 374 str(@tmp_int, 10) + space(5) + 375 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 376 @psign 377 print @rptline 378 379 print @sum1line 380 select @rptline = " Total EX-Intent Requests" + space(3) + 381 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 382 space(2) + 383 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 384 space(2) + 385 str(@tmp_total, 10) + space(5) + 386 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + 387 @psign 388 print @rptline 389 end 390 print @blankline 391 392 print " Shared Intent" 393 394 select @tmp_total = SUM(value) 395 from #tempmonitors 396 where group_name = "lock" and 397 (field_name like "granted%_SH_INT" or 398 field_name like "waited%_SH_INT") 399 400 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 401 begin 402 select @rptline = " Total SH-Intent Requests 0.0 0.0 0 n/a" 403 print @rptline 404 end 405 else 406 begin 407 select @tmp_int = sum(value) 408 from #tempmonitors 409 where group_name = "lock" and 410 field_name like "granted%_SH_INT" 411 412 select @rptline = " Granted" + space(18) + 413 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 414 space(2) + 415 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 416 space(2) + 417 str(@tmp_int, 10) + space(5) + 418 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 419 @psign 420 print @rptline 421 422 select @tmp_int = sum(value) 423 from #tempmonitors 424 where group_name = "lock" and 425 field_name like "waited%_SH_INT" 426 427 select @rptline = " Waited" + space(19) + 428 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 429 space(2) + 430 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 431 space(2) + 432 str(@tmp_int, 10) + space(5) + 433 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 434 @psign 435 print @rptline 436 print @sum1line 437 438 select @rptline = " Total SH-Intent Requests" + space(3) + 439 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 440 space(2) + 441 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 442 space(2) + 443 str(@tmp_total, 10) + space(5) + 444 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + 445 @psign 446 print @rptline 447 end 448 print @blankline 449 450 print " Page & Row Lock HashTable" 451 select @tmp_int = value 452 from #tempmonitors 453 where group_name = "lock" and 454 field_name = "hashtab_lookups_pagerow_lock" 455 456 select @rptline = " Lookups" + space(18) + 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(7) + @na_str 462 print @rptline 463 464 /* Avoid divide by 0 error */ 465 if (@tmp_int > 0) 466 begin 467 select @tmp_int2 = value 468 from #tempmonitors 469 where group_name = "lock" and 470 field_name = "hashtab_overflow_pagerow_lock" 471 472 select @rptline = " Avg Chain Length" + space(18) + 473 @na_str + space(11) + 474 @na_str + space(2) + 475 str(convert(real, @tmp_int2) / @tmp_int, 10, 5) + 476 space(7) + @na_str 477 , @reco_avg_chain_length_pagerow = convert(real, @tmp_int2) / @tmp_int 478 print @rptline 479 end 480 else 481 begin 482 select @reco_avg_chain_length_pagerow = 0 483 end 484 485 select @spinlock_contention = isnull(100.0 * (sum(waits) / sum(grabs)), 0) 486 from #foo 487 where name = 'fglockspins' 488 and grabs > 0 489 490 select @rptline = " Spinlock Contention" + space(15) + 491 @na_str + space(11) + 492 @na_str + space(9) + 493 @na_str + space(5) + 494 str(@spinlock_contention, 5, 1) 495 + @psign 496 , @reco_fgspin_contention = @spinlock_contention 497 print @rptline 498 print @blankline 499 500 print " Exclusive Page" 501 502 select @tmp_total = SUM(value) 503 from #tempmonitors 504 where group_name = "lock" and 505 (field_name like "granted%_EX_PAGE" or 506 field_name like "waited%_EX_PAGE") 507 508 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 509 begin 510 select @rptline = " Total EX-Page Requests 0.0 0.0 0 n/a" 511 print @rptline 512 end 513 else 514 begin 515 516 select @tmp_int = sum(value) 517 from #tempmonitors 518 where group_name = "lock" and 519 field_name like "granted%_EX_PAGE" 520 521 select @rptline = " Granted" + space(18) + 522 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 523 space(2) + 524 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 525 space(2) + 526 str(@tmp_int, 10) + space(5) + 527 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 528 @psign 529 print @rptline 530 531 select @tmp_int = sum(value) 532 from #tempmonitors 533 where group_name = "lock" and 534 field_name like "waited%_EX_PAGE" 535 536 select @rptline = " Waited" + space(19) + 537 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 538 space(2) + 539 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 540 space(2) + 541 str(@tmp_int, 10) + space(5) + 542 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 543 print @rptline 544 545 print @sum1line 546 select @rptline = " Total EX-Page Requests" + space(5) + 547 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 548 space(2) + 549 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 550 space(2) + 551 str(@tmp_total, 10) + space(5) + 552 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 553 print @rptline 554 end 555 print @blankline 556 557 print " Update Page" 558 559 select @tmp_total = SUM(value) 560 from #tempmonitors 561 where group_name = "lock" and 562 (field_name like "granted%_UP_PAGE" or 563 field_name like "waited%_UP_PAGE") 564 565 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 566 begin 567 select @rptline = " Total UP-Page Requests 0.0 0.0 0 n/a" 568 print @rptline 569 end 570 else 571 begin 572 573 select @tmp_int = sum(value) 574 from #tempmonitors 575 where group_name = "lock" and 576 field_name like "granted%_UP_PAGE" 577 578 select @rptline = " Granted" + space(18) + 579 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 580 space(2) + 581 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 582 space(2) + 583 str(@tmp_int, 10) + space(5) + 584 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 585 print @rptline 586 587 select @tmp_int = sum(value) 588 from #tempmonitors 589 where group_name = "lock" and 590 field_name like "waited%_UP_PAGE" 591 592 select @rptline = " Waited" + space(19) + 593 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 594 space(2) + 595 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 596 space(2) + 597 str(@tmp_int, 10) + space(5) + 598 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 599 print @rptline 600 601 print @sum1line 602 select @rptline = " Total UP-Page Requests" + space(5) + 603 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 604 space(2) + 605 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 606 space(2) + 607 str(@tmp_total, 10) + space(5) + 608 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 609 print @rptline 610 end 611 print @blankline 612 613 print " Shared Page" 614 615 select @tmp_total = SUM(value) 616 from #tempmonitors 617 where group_name = "lock" and 618 (field_name like "granted%_SH_PAGE" or 619 field_name like "waited%_SH_PAGE") 620 621 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 622 begin 623 select @rptline = " Total SH-Page Requests 0.0 0.0 0 n/a" 624 print @rptline 625 end 626 else 627 begin 628 629 select @tmp_int = sum(value) 630 from #tempmonitors 631 where group_name = "lock" and 632 field_name like "granted%_SH_PAGE" 633 634 select @rptline = " Granted" + space(18) + 635 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 636 space(2) + 637 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 638 space(2) + 639 str(@tmp_int, 10) + space(5) + 640 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 641 print @rptline 642 643 select @tmp_int = sum(value) 644 from #tempmonitors 645 where group_name = "lock" and 646 field_name like "waited%_SH_PAGE" 647 648 select @rptline = " Waited" + space(19) + 649 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 650 space(2) + 651 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 652 space(2) + 653 str(@tmp_int, 10) + space(5) + 654 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 655 print @rptline 656 657 print @sum1line 658 select @rptline = " Total SH-Page Requests" + space(5) + 659 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 660 space(2) + 661 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 662 space(2) + 663 str(@tmp_total, 10) + space(5) + 664 665 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 666 print @rptline 667 print @blankline 668 end 669 print @blankline 670 671 print " Exclusive Row" 672 673 select @tmp_total = SUM(value) 674 from #tempmonitors 675 where group_name = "lock" and 676 (field_name like "granted%_EX_ROW" or 677 field_name like "waited%_EX_ROW") 678 679 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 680 begin 681 select @rptline = " Total EX-Row Requests 0.0 0.0 0 n/a" 682 print @rptline 683 end 684 else 685 begin 686 687 select @tmp_int = sum(value) 688 from #tempmonitors 689 where group_name = "lock" and 690 field_name like "granted%_EX_ROW" 691 692 select @rptline = " Granted" + space(18) + 693 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 694 space(2) + 695 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 696 space(2) + 697 str(@tmp_int, 10) + space(5) + 698 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 699 @psign 700 print @rptline 701 702 select @tmp_int = sum(value) 703 from #tempmonitors 704 where group_name = "lock" and 705 field_name like "waited%_EX_ROW" 706 707 select @rptline = " Waited" + space(19) + 708 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 709 space(2) + 710 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 711 space(2) + 712 str(@tmp_int, 10) + space(5) + 713 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 714 print @rptline 715 716 print @sum1line 717 select @rptline = " Total EX-Row Requests" + space(6) + 718 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 719 space(2) + 720 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 721 space(2) + 722 str(@tmp_total, 10) + space(5) + 723 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 724 print @rptline 725 end 726 print @blankline 727 728 print " Update Row" 729 730 select @tmp_total = SUM(value) 731 from #tempmonitors 732 where group_name = "lock" and 733 (field_name like "granted%_UP_ROW" or 734 field_name like "waited%_UP_ROW") 735 736 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 737 begin 738 select @rptline = " Total UP-Row Requests 0.0 0.0 0 n/a" 739 print @rptline 740 end 741 else 742 begin 743 744 select @tmp_int = sum(value) 745 from #tempmonitors 746 where group_name = "lock" and 747 field_name like "granted%_UP_ROW" 748 749 select @rptline = " Granted" + space(18) + 750 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 751 space(2) + 752 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 753 space(2) + 754 str(@tmp_int, 10) + space(5) + 755 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 756 print @rptline 757 758 select @tmp_int = sum(value) 759 from #tempmonitors 760 where group_name = "lock" and 761 field_name like "waited%_UP_ROW" 762 763 select @rptline = " Waited" + space(19) + 764 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 765 space(2) + 766 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 767 space(2) + 768 str(@tmp_int, 10) + space(5) + 769 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 770 print @rptline 771 772 print @sum1line 773 select @rptline = " Total UP-Row Requests" + space(6) + 774 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 775 space(2) + 776 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 777 space(2) + 778 str(@tmp_total, 10) + space(5) + 779 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 780 print @rptline 781 end 782 print @blankline 783 784 print " Shared Row" 785 786 select @tmp_total = SUM(value) 787 from #tempmonitors 788 where group_name = "lock" and 789 (field_name like "granted%_SH_ROW" or 790 field_name like "waited%_SH_ROW") 791 792 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 793 begin 794 select @rptline = " Total SH-Row Requests 0.0 0.0 0 n/a" 795 print @rptline 796 end 797 else 798 begin 799 800 select @tmp_int = sum(value) 801 from #tempmonitors 802 where group_name = "lock" and 803 field_name like "granted%_SH_ROW" 804 805 select @rptline = " Granted" + space(18) + 806 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 807 space(2) + 808 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 809 space(2) + 810 str(@tmp_int, 10) + space(5) + 811 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 812 print @rptline 813 814 select @tmp_int = sum(value) 815 from #tempmonitors 816 where group_name = "lock" and 817 field_name like "waited%_SH_ROW" 818 819 select @rptline = " Waited" + space(19) + 820 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 821 space(2) + 822 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 823 space(2) + 824 str(@tmp_int, 10) + space(5) + 825 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 826 print @rptline 827 828 print @sum1line 829 select @rptline = " Total SH-Row Requests" + space(6) + 830 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 831 space(2) + 832 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 833 space(2) + 834 str(@tmp_total, 10) + space(5) + 835 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 836 print @rptline 837 print @blankline 838 end 839 print @blankline 840 841 print " Next-Key" 842 843 select @tmp_total = SUM(value) 844 from #tempmonitors 845 where group_name = "lock" and 846 (field_name like "granted%_SH_NKL" or 847 field_name like "waited%_SH_NKL") 848 849 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 850 begin 851 select @rptline = " Total Next-Key Requests 0.0 0.0 0 n/a" 852 print @rptline 853 end 854 else 855 begin 856 857 select @tmp_int = sum(value) 858 from #tempmonitors 859 where group_name = "lock" and 860 field_name like "granted%_SH_NKL" 861 862 select @rptline = " Granted" + space(18) + 863 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 864 space(2) + 865 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 866 space(2) + 867 str(@tmp_int, 10) + space(5) + 868 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 869 print @rptline 870 871 select @tmp_int = sum(value) 872 from #tempmonitors 873 where group_name = "lock" and 874 field_name like "waited%_SH_NKL" 875 876 select @rptline = " Waited" + space(19) + 877 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 878 space(2) + 879 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 880 space(2) + 881 str(@tmp_int, 10) + space(5) + 882 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 883 print @rptline 884 885 print @sum1line 886 select @rptline = " Total Next-Key Requests" + space(4) + 887 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 888 space(2) + 889 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 890 space(2) + 891 str(@tmp_total, 10) + space(5) + 892 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 893 print @rptline 894 print @blankline 895 end 896 print @blankline 897 898 print " Address Lock Hashtable" 899 select @tmp_int = value 900 from #tempmonitors 901 where group_name = "lock" and 902 field_name = "hashtab_lookups_address_lock" 903 904 select @rptline = " Lookups" + space(18) + 905 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 906 space(2) + 907 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 908 space(2) + 909 str(@tmp_int, 10) + space(7) + 910 @na_str 911 print @rptline 912 913 /* Avoid divide by 0 error */ 914 if (@tmp_int > 0) 915 begin 916 select @tmp_int2 = value 917 from #tempmonitors 918 where group_name = "lock" and 919 field_name = "hashtab_overflow_address_lock" 920 921 select @rptline = " Avg Chain Length" + space(18) + 922 @na_str + space(11) + 923 @na_str + space(2) + 924 str(convert(real, @tmp_int2) / @tmp_int, 10, 5) + 925 space(7) + @na_str 926 print @rptline 927 end 928 929 select @spinlock_contention = isnull(100.0 * (sum(waits) / sum(grabs)), 0) 930 from #foo 931 where name = 'addrlockspins' 932 and grabs > 0 933 934 select @rptline = " Spinlock Contention" + space(15) + 935 @na_str + space(11) + 936 @na_str + space(9) + 937 @na_str + space(5) + 938 str(@spinlock_contention, 5, 1) 939 + @psign 940 , @reco_addrspin_contention = @spinlock_contention 941 print @rptline 942 print @blankline 943 944 print " Exclusive Address" 945 946 select @tmp_total = SUM(value) 947 from #tempmonitors 948 where group_name = "lock" and 949 (field_name like "granted_%EX_ADDR" or 950 field_name like "waited_%EX_ADDR") 951 952 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 953 begin 954 select @rptline = " Total EX-Address Requests 0.0 0.0 0 n/a" 955 print @rptline 956 end 957 else 958 begin 959 960 select @tmp_int = sum(value) 961 from #tempmonitors 962 where group_name = "lock" and 963 field_name like "granted_%EX_ADDR" 964 965 select @rptline = " Granted" + space(18) + 966 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 967 space(2) + 968 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 969 space(2) + 970 str(@tmp_int, 10) + space(5) + 971 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 972 print @rptline 973 974 select @tmp_int = sum(value) 975 from #tempmonitors 976 where group_name = "lock" and 977 field_name like "waited_%EX_ADDR" 978 979 select @rptline = " Waited" + space(19) + 980 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 981 space(2) + 982 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 983 space(2) + 984 str(@tmp_int, 10) + space(5) + 985 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 986 print @rptline 987 988 print @sum1line 989 select @rptline = " Total EX-Address Requests " + 990 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 991 space(2) + 992 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 993 space(2) + 994 str(@tmp_total, 10) + space(5) + 995 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 996 print @rptline 997 998 end 999 print @blankline 1000 1001 print " Shared Address" 1002 1003 select @tmp_total = SUM(value) 1004 from #tempmonitors 1005 where group_name = "lock" and 1006 (field_name like "granted_%SH_ADDR" or 1007 field_name like "waited_%SH_ADDR") 1008 1009 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 1010 begin 1011 select @rptline = " Total SH-Address Requests 0.0 0.0 0 n/a" 1012 print @rptline 1013 end 1014 else 1015 begin 1016 1017 select @tmp_int = sum(value) 1018 from #tempmonitors 1019 where group_name = "lock" and 1020 field_name like "granted_%SH_ADDR" 1021 1022 select @rptline = " Granted" + space(18) + 1023 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1024 space(2) + 1025 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1026 space(2) + 1027 str(@tmp_int, 10) + space(5) + 1028 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1029 print @rptline 1030 1031 select @tmp_int = sum(value) 1032 from #tempmonitors 1033 where group_name = "lock" and 1034 field_name like "waited_%SH_ADDR" 1035 1036 select @rptline = " Waited" + space(19) + 1037 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1038 space(2) + 1039 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1040 space(2) + 1041 str(@tmp_int, 10) + space(5) + 1042 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1043 print @rptline 1044 1045 print @sum1line 1046 select @rptline = " Total SH-Address Requests " + 1047 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 1048 space(2) + 1049 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1050 space(2) + 1051 str(@tmp_total, 10) + space(5) + 1052 str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign 1053 print @rptline 1054 1055 print @blankline 1056 end 1057 print @blankline 1058 1059 print " Last Page Locks on Heaps" 1060 1061 select @tmp_total = SUM(value) 1062 from #tempmonitors 1063 where group_name = "access" and 1064 field_name = "locklastpage" 1065 1066 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 1067 begin 1068 select @rptline = " Total Last Pg Locks 0.0 0.0 0 n/a" 1069 print @rptline 1070 end 1071 else 1072 begin 1073 1074 select @tmp_int2 = value 1075 from #tempmonitors 1076 where group_name = "access" and 1077 field_name = "lklastpg_wait" 1078 1079 select @tmp_int = @tmp_total - @tmp_int2 1080 1081 select @rptline = " Granted" + space(18) + 1082 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1083 space(2) + 1084 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1085 space(2) + 1086 str(@tmp_int, 10) + space(5) + 1087 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1088 print @rptline 1089 1090 select @reco_lastpg_wait_percent = convert(int, 1091 100.0 * ((1.0 * @tmp_int2) / @tmp_total)) 1092 1093 select @rptline = " Waited" + space(19) + 1094 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) + 1095 space(2) + 1096 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) + 1097 space(2) + 1098 str(@tmp_int2, 10) + space(5) + 1099 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign 1100 print @rptline 1101 print @sum1line 1102 select @rptline = " Total Last Pg Locks" + space(8) + 1103 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 1104 space(2) + 1105 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1106 space(2) + 1107 str(@tmp_total, 10) + space(5) + 1108 str(100.0 * @tmp_total / @tmp_total, 5, 1) + @psign 1109 print @rptline 1110 print @blankline 1111 end 1112 print @blankline 1113 end 1114 1115 1116 print " Deadlocks by Lock Type per sec per xact count %% of total" 1117 print @sum1line 1118 1119 select @tmp_total = value 1120 from #tempmonitors 1121 where group_name = "lock" and 1122 field_name = "deadlocks" 1123 1124 select @totaldeadlocks = sum(value) 1125 from #tempmonitors 1126 where group_name like "buffer_%" and 1127 field_name like "physical_lock_deadlock" 1128 1129 select @tmp_access = SUM(value) 1130 from #tempmonitors 1131 where group_name = "access" and 1132 field_name in ("getrootpg_deadlock", 1133 "lkindex_TSCK_deadlock", 1134 "lkindex_deadlock", 1135 "lkindDS_P_deadlock", 1136 "lkindDS_C_deadlock", 1137 "split_index_deadlock", 1138 "log_lock_deadlock") 1139 1140 select @tmp_xls = value 1141 from #tempmonitors 1142 where group_name = "xls" and 1143 field_name = "log_lock_deadlock" 1144 1145 select @tmp_total = @tmp_total + @totaldeadlocks + @tmp_access + @tmp_xls 1146 1147 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 1148 begin 1149 select @rptline = " Total Deadlocks 0.0 0.0 0 n/a" 1150 print @rptline 1151 end 1152 else 1153 begin 1154 1155 select @tmp_int = value 1156 from #tempmonitors 1157 where group_name = "lock" and 1158 field_name = "deadlock_EX_TAB" 1159 1160 select @rptline = " Exclusive Table" + space(10) + 1161 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1162 space(2) + 1163 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1164 space(2) + 1165 str(@tmp_int, 10) + space(5) + 1166 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1167 print @rptline 1168 1169 select @tmp_int = value 1170 from #tempmonitors 1171 where group_name = "lock" and 1172 field_name = "deadlock_SH_TAB" 1173 1174 select @rptline = " Shared Table" + space(13) + 1175 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1176 space(2) + 1177 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1178 space(2) + 1179 str(@tmp_int, 10) + space(5) + 1180 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1181 print @rptline 1182 1183 select @tmp_int = value 1184 from #tempmonitors 1185 where group_name = "lock" and 1186 field_name = "deadlock_EX_INT" 1187 1188 select @rptline = " Exclusive Intent" + space(9) + 1189 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1190 space(2) + 1191 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1192 space(2) + 1193 str(@tmp_int, 10) + space(5) + 1194 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1195 print @rptline 1196 1197 select @tmp_int = value 1198 from #tempmonitors 1199 where group_name = "lock" and 1200 field_name = "deadlock_SH_INT" 1201 1202 select @rptline = " Shared Intent" + space(12) + 1203 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1204 space(2) + 1205 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1206 space(2) + 1207 str(@tmp_int, 10) + space(5) + 1208 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1209 print @rptline 1210 1211 select @tmp_int = value 1212 from #tempmonitors 1213 where group_name = "lock" and 1214 field_name = "deadlock_EX_PAGE" 1215 1216 select @rptline = " Exclusive Page" + space(11) + 1217 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1218 space(2) + 1219 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1220 space(2) + 1221 str(@tmp_int, 10) + space(5) + 1222 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1223 print @rptline 1224 1225 select @tmp_int = value 1226 from #tempmonitors 1227 where group_name = "lock" and 1228 field_name = "deadlock_UP_PAGE" 1229 1230 select @rptline = " Update Page" + space(14) + 1231 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1232 space(2) + 1233 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1234 space(2) + 1235 str(@tmp_int, 10) + space(5) + 1236 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1237 print @rptline 1238 1239 select @tmp_int = value 1240 from #tempmonitors 1241 where group_name = "lock" and 1242 field_name = "deadlock_SH_PAGE" 1243 1244 select @rptline = " Shared Page" + space(14) + 1245 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1246 space(2) + 1247 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1248 space(2) + 1249 str(@tmp_int, 10) + space(5) + 1250 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1251 print @rptline 1252 1253 select @tmp_int = value 1254 from #tempmonitors 1255 where group_name = "lock" and 1256 field_name = "deadlock_EX_ROW" 1257 1258 select @rptline = " Exclusive Row" + space(12) + 1259 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1260 space(2) + 1261 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1262 space(2) + 1263 str(@tmp_int, 10) + space(5) + 1264 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1265 print @rptline 1266 1267 select @tmp_int = value 1268 from #tempmonitors 1269 where group_name = "lock" and 1270 field_name = "deadlock_UP_ROW" 1271 1272 select @rptline = " Update Row" + space(15) + 1273 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1274 space(2) + 1275 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1276 space(2) + 1277 str(@tmp_int, 10) + space(5) + 1278 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1279 print @rptline 1280 1281 select @tmp_int = value 1282 from #tempmonitors 1283 where group_name = "lock" and 1284 field_name = "deadlock_SH_ROW" 1285 1286 select @rptline = " Shared Row" + space(15) + 1287 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1288 space(2) + 1289 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1290 space(2) + 1291 str(@tmp_int, 10) + space(5) + 1292 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1293 print @rptline 1294 1295 select @tmp_int = value 1296 from #tempmonitors 1297 where group_name = "lock" and 1298 field_name = "deadlock_SH_NKL" 1299 1300 select @rptline = " Shared Next-Key" + space(10) + 1301 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1302 space(2) + 1303 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1304 space(2) + 1305 str(@tmp_int, 10) + space(5) + 1306 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1307 print @rptline 1308 1309 1310 select @tmp_int = value 1311 from #tempmonitors 1312 where group_name = "lock" and 1313 field_name = "deadlock_EX_ADDR" 1314 1315 select @rptline = " Exclusive Address" + space(8) + 1316 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1317 space(2) + 1318 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1319 space(2) + 1320 str(@tmp_int, 10) + space(5) + 1321 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1322 print @rptline 1323 1324 select @tmp_int = value 1325 from #tempmonitors 1326 where group_name = "lock" and 1327 field_name = "deadlock_SH_ADDR" 1328 1329 select @rptline = " Shared Address" + space(11) + 1330 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1331 space(2) + 1332 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1333 space(2) + 1334 str(@tmp_int, 10) + space(5) + 1335 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1336 print @rptline 1337 1338 /* Get the deadlocks for physical lock acquisition */ 1339 select @rptline = " Physical Locks " + space(5) + 1340 str(@totaldeadlocks / @NumElapsedSec, 12, 1) + 1341 space(2) + 1342 str(@totaldeadlocks / convert(real, @NumXacts), 12, 1) + 1343 space(2) + 1344 str(@totaldeadlocks, 10) + space(5) + 1345 str(100.0 * @totaldeadlocks / @tmp_total, 5, 1) + @psign 1346 print @rptline 1347 1348 /* Get the deadlocks for "access" group */ 1349 select @tmp_access = SUM(value) 1350 from #tempmonitors 1351 where group_name = "access" and 1352 field_name in ("getrootpg_deadlock", 1353 "lkindex_TSCK_deadlock", 1354 "lkindex_deadlock", 1355 "lkindDS_P_deadlock", 1356 "lkindDS_C_deadlock", 1357 "split_index_deadlock", 1358 "log_lock_deadlock") 1359 1360 /* Get the deadlocks for "xls" group */ 1361 select @tmp_xls = value 1362 from #tempmonitors 1363 where group_name = "xls" and 1364 field_name = "log_lock_deadlock" 1365 1366 /* Get the sum of the deadlocks(other than belonging to group locks) */ 1367 select @tmp_int = @tmp_access + @tmp_xls 1368 1369 select @rptline = " Others" + space(19) + 1370 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1371 space(2) + 1372 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1373 space(2) + 1374 str(@tmp_int, 10) + space(5) + 1375 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1376 print @rptline 1377 1378 1379 print @sum2line 1380 select @rptline = " Total Deadlocks" + space(12) + 1381 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 1382 space(2) + 1383 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1384 space(2) + 1385 str(@tmp_total, 10) 1386 print @rptline 1387 end 1388 1389 print @blankline 1390 select @tmp_total = value 1391 from #tempmonitors 1392 where group_name = "locks" and 1393 field_name = "clm_deadlocks" 1394 1395 if @tmp_total != 0 /* Avoid Divide by Zero Errors */ 1396 begin 1397 1398 select @rptline = " Total Cluster Deadlocks" + space(4) + 1399 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 1400 space(2) + 1401 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1402 space(2) + 1403 str(@tmp_total, 10) 1404 print @rptline 1405 end 1406 1407 print @blankline 1408 print @blankline 1409 1410 select @tmp_int2 = @tmp_total /* save for deadlock search calc */ 1411 1412 print " Deadlock Detection" 1413 1414 select @tmp_total = value 1415 from #tempmonitors 1416 where group_name = "lock" and 1417 field_name = "deadlock_search" 1418 1419 select @rptline = " Deadlock Searches" + space(8) + 1420 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 1421 space(2) + 1422 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1423 space(2) + 1424 str(@tmp_total, 10) + space(7) + 1425 @na_str 1426 print @rptline 1427 1428 if @tmp_total != 0 /* Avoid Divide by Zero Errors */ 1429 begin 1430 1431 select @tmp_int = value 1432 from #tempmonitors 1433 where group_name = "lock" and 1434 field_name = "deadlock_alarm_skipped" 1435 1436 select @rptline = " Searches Skipped" + space(9) + 1437 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1438 space(2) + 1439 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1440 space(2) + 1441 str(@tmp_int, 10) + space(5) + 1442 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1443 print @rptline 1444 1445 select @rptline = " Avg Deadlocks per Search" + space(10) + 1446 @na_str + 1447 space(11) + 1448 @na_str + 1449 space(2) + 1450 str(convert(real, @tmp_int2) / @tmp_total, 10, 5) + 1451 space(7) + 1452 @na_str 1453 print @rptline 1454 end 1455 print @blankline 1456 print @blankline 1457 1458 print " Lock Promotions" 1459 1460 select @tmp_total = SUM(value) 1461 from #tempmonitors 1462 where group_name = "lock" and 1463 (field_name like "promoted_%_PAGE" 1464 or field_name like "promoted_%_ROW" 1465 or field_name like "promoted_%_NKL") 1466 1467 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 1468 begin 1469 select @rptline = " Total Lock Promotions 0.0 0.0 0 n/a" 1470 print @rptline 1471 end 1472 else 1473 begin 1474 1475 select @tmp_int = value 1476 from #tempmonitors 1477 where group_name = "lock" and 1478 field_name = "promoted_EX_PAGE" 1479 1480 select @rptline = " Ex-Page to Ex-Table" + space(6) + 1481 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1482 space(2) + 1483 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1484 space(2) + 1485 str(@tmp_int, 10) + space(5) + 1486 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1487 print @rptline 1488 1489 select @tmp_int = value 1490 from #tempmonitors 1491 where group_name = "lock" and 1492 field_name = "promoted_SH_PAGE" 1493 1494 select @rptline = " Sh-Page to Sh-Table" + space(6) + 1495 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1496 space(2) + 1497 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1498 space(2) + 1499 str(@tmp_int, 10) + space(5) + 1500 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1501 print @rptline 1502 1503 select @tmp_int = value 1504 from #tempmonitors 1505 where group_name = "lock" and 1506 field_name = "promoted_EX_ROW" 1507 1508 select @rptline = " Ex-Row to Ex-Table" + space(7) + 1509 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1510 space(2) + 1511 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1512 space(2) + 1513 str(@tmp_int, 10) + space(5) + 1514 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1515 print @rptline 1516 1517 select @tmp_int = value 1518 from #tempmonitors 1519 where group_name = "lock" and 1520 field_name = "promoted_SH_ROW" 1521 1522 select @rptline = " Sh-Row to Sh-Table" + space(7) + 1523 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1524 space(2) + 1525 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1526 space(2) + 1527 str(@tmp_int, 10) + space(5) + 1528 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1529 print @rptline 1530 1531 select @tmp_int = value 1532 from #tempmonitors 1533 where group_name = "lock" and 1534 field_name = "promoted_SH_NKL" 1535 1536 select @rptline = " Sh-Next-Key to Sh-Table" + space(2) + 1537 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1538 space(2) + 1539 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1540 space(2) + 1541 str(@tmp_int, 10) + space(5) + 1542 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1543 print @rptline 1544 1545 print @sum2line 1546 select @rptline = " Total Lock Promotions" + space(6) + 1547 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 1548 space(2) + 1549 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1550 space(2) + 1551 str(@tmp_total, 10) 1552 print @rptline 1553 end 1554 print @blankline 1555 print @blankline 1556 1557 print " Lock Timeouts by Lock Type per sec per xact count %% of total" 1558 print @sum1line 1559 1560 select @tmp_total = sum(value) 1561 from #tempmonitors 1562 where group_name = "lock" and 1563 field_name like "timedout%" 1564 1565 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 1566 begin 1567 select @rptline = " Total Timeouts 0.0 0.0 0 n/a" 1568 print @rptline 1569 end 1570 else 1571 begin 1572 1573 select @tmp_int = value 1574 from #tempmonitors 1575 where group_name = "lock" and 1576 field_name = "timedout_EX_TAB" 1577 1578 select @rptline = " Exclusive Table" + space(10) + 1579 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1580 space(2) + 1581 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1582 space(2) + 1583 str(@tmp_int, 10) + space(5) + 1584 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1585 print @rptline 1586 1587 select @tmp_int = value 1588 from #tempmonitors 1589 where group_name = "lock" and 1590 field_name = "timedout_SH_TAB" 1591 1592 select @rptline = " Shared Table" + space(13) + 1593 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1594 space(2) + 1595 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1596 space(2) + 1597 str(@tmp_int, 10) + space(5) + 1598 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1599 print @rptline 1600 1601 select @tmp_int = value 1602 from #tempmonitors 1603 where group_name = "lock" and 1604 field_name = "timedout_EX_INT" 1605 1606 select @rptline = " Exclusive Intent" + space(9) + 1607 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1608 space(2) + 1609 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1610 space(2) + 1611 str(@tmp_int, 10) + space(5) + 1612 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1613 print @rptline 1614 1615 select @tmp_int = value 1616 from #tempmonitors 1617 where group_name = "lock" and 1618 field_name = "timedout_SH_INT" 1619 1620 select @rptline = " Shared Intent" + space(12) + 1621 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1622 space(2) + 1623 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1624 space(2) + 1625 str(@tmp_int, 10) + space(5) + 1626 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1627 print @rptline 1628 1629 select @tmp_int = value 1630 from #tempmonitors 1631 where group_name = "lock" and 1632 field_name = "timedout_EX_PAGE" 1633 1634 select @rptline = " Exclusive Page" + space(11) + 1635 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1636 space(2) + 1637 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1638 space(2) + 1639 str(@tmp_int, 10) + space(5) + 1640 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1641 print @rptline 1642 1643 select @tmp_int = value 1644 from #tempmonitors 1645 where group_name = "lock" and 1646 field_name = "timedout_UP_PAGE" 1647 1648 select @rptline = " Update Page" + space(14) + 1649 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1650 space(2) + 1651 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1652 space(2) + 1653 str(@tmp_int, 10) + space(5) + 1654 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1655 print @rptline 1656 1657 select @tmp_int = value 1658 from #tempmonitors 1659 where group_name = "lock" and 1660 field_name = "timedout_SH_PAGE" 1661 1662 select @rptline = " Shared Page" + space(14) + 1663 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1664 space(2) + 1665 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1666 space(2) + 1667 str(@tmp_int, 10) + space(5) + 1668 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1669 print @rptline 1670 1671 select @tmp_int = value 1672 from #tempmonitors 1673 where group_name = "lock" and 1674 field_name = "timedout_EX_ROW" 1675 1676 select @rptline = " Exclusive Row" + space(12) + 1677 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1678 space(2) + 1679 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1680 space(2) + 1681 str(@tmp_int, 10) + space(5) + 1682 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1683 print @rptline 1684 1685 select @tmp_int = value 1686 from #tempmonitors 1687 where group_name = "lock" and 1688 field_name = "timedout_UP_ROW" 1689 1690 select @rptline = " Update Row" + space(15) + 1691 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1692 space(2) + 1693 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1694 space(2) + 1695 str(@tmp_int, 10) + space(5) + 1696 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1697 print @rptline 1698 1699 select @tmp_int = value 1700 from #tempmonitors 1701 where group_name = "lock" and 1702 field_name = "timedout_SH_ROW" 1703 1704 select @rptline = " Shared Row" + space(15) + 1705 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1706 space(2) + 1707 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1708 space(2) + 1709 str(@tmp_int, 10) + space(5) + 1710 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1711 print @rptline 1712 1713 select @tmp_int = value 1714 from #tempmonitors 1715 where group_name = "lock" and 1716 field_name = "timedout_EX_ADDR" 1717 1718 select @rptline = " Exclusive Address" + space(8) + 1719 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1720 space(2) + 1721 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1722 space(2) + 1723 str(@tmp_int, 10) + space(5) + 1724 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1725 print @rptline 1726 1727 select @tmp_int = value 1728 from #tempmonitors 1729 where group_name = "lock" and 1730 field_name = "timedout_SH_ADDR" 1731 1732 select @rptline = " Shared Address" + space(11) + 1733 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1734 space(2) + 1735 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1736 space(2) + 1737 str(@tmp_int, 10) + space(5) + 1738 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1739 print @rptline 1740 1741 select @tmp_int = value 1742 from #tempmonitors 1743 where group_name = "lock" and 1744 field_name = "timedout_SH_NKL" 1745 1746 select @rptline = " Shared Next-Key" + space(10) + 1747 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 1748 space(2) + 1749 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1750 space(2) + 1751 str(@tmp_int, 10) + space(5) + 1752 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1753 print @rptline 1754 1755 print @sum2line 1756 1757 select @rptline = " Total Lock Timeouts" + space(8) + 1758 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 1759 space(2) + 1760 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1761 space(2) + 1762 str(@tmp_total, 10) 1763 print @rptline 1764 end 1765 1766 print @blankline 1767 print " Cluster Lock Summary per sec per xact count %% of total" 1768 print @sum1line 1769 1770 select @tmp_total = sum(value) 1771 from #tempmonitors 1772 where group_name like "lock" and 1773 field_name like "clm_lock_gc" 1774 select @tmp_int = sum(value) 1775 from #tempmonitors 1776 where group_name like "lock" and 1777 field_name like "clm_lockgc_target_collected" 1778 1779 if @tmp_total != 0 /* Avoid Divide by Zero Errors after printout */ 1780 begin 1781 print @blankline 1782 select @rptline = " Lock Garbage Collection " + 1783 str(@tmp_total / @NumElapsedSec, 12, 1) + 1784 space(2) + 1785 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1786 space(2) + 1787 str(@tmp_total, 10) + space(7) + 1788 @na_str 1789 1790 print @rptline 1791 select @rptline = " Targeted Collection Sucess" + 1792 space(7) + 1793 @na_str + 1794 space(11) + 1795 @na_str + 1796 space(2) + 1797 str(@tmp_int, 10) + space(5) + 1798 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1799 print @rptline 1800 print @blankline 1801 end 1802 1803 print @blankline 1804 print " Physical Locks Summary per sec per xact count %% of total" 1805 print @sum1line 1806 1807 select @totalrequested = sum(value) 1808 from #tempmonitors 1809 where group_name like "buffer_%" and 1810 field_name like "physical_lock_acquisition" 1811 1812 select @totalretained = sum(value) 1813 from #tempmonitors 1814 where group_name like "buffer_%" and 1815 field_name like "physical_lock_retented" 1816 1817 select @totalwaits = sum(value) 1818 from #tempmonitors 1819 where group_name like "lock" and 1820 field_name like "physical_lock_waits" 1821 1822 select @totaltransfers = sum(value) 1823 from #tempmonitors 1824 where group_name like "buffer_%" and 1825 field_name like "physical_lock_txrecv" 1826 1827 select @totaldiskreads = sum(value) 1828 from #tempmonitors 1829 where group_name like "buffer_%" and 1830 field_name like "diskread_lockmgr" 1831 1832 select @totallockmgrcalls = sum(value) 1833 from #tempmonitors 1834 where group_name like "buffer_%" and 1835 field_name like "physical_lock_txrecv" 1836 1837 if @totalrequested = 0 1838 begin 1839 print "No physical locks are acquired" 1840 end 1841 else 1842 begin 1843 select @rptline = " Total Lock Requests" + space(6) + 1844 str(@totalrequested / @NumElapsedSec, 12, 1) + 1845 space(2) + 1846 str(@totalrequested / convert(real, @NumXacts), 12, 1) + 1847 space(2) + 1848 str(@totalrequested, 10) + space(5) + 1849 str(100.0 * @totalrequested / @totalrequested, 5, 1) + @psign 1850 print @rptline 1851 1852 select @rptline = " Retained Locks " + space(7) + 1853 str(@totalretained / @NumElapsedSec, 12, 1) + 1854 space(2) + 1855 str(@totalretained / convert(real, @NumXacts), 12, 1) + 1856 space(2) + 1857 str(@totalretained, 10) + space(5) + 1858 str(100.0 * @totalretained / @totalrequested, 5, 1) + @psign 1859 print @rptline 1860 1861 select @rptline = " Non-retained Locks" + space(7) + 1862 str((@totalrequested - @totalretained) / 1863 @NumElapsedSec, 12, 1) + 1864 space(2) + 1865 str((@totalrequested - @totalretained) / 1866 convert(real, @NumXacts), 12, 1) + 1867 space(2) + 1868 str((@totalrequested - @totalretained), 10) + space(5) + 1869 str(100.0 * (@totalrequested - @totalretained) / 1870 @totalrequested, 5, 1) + @psign 1871 print @rptline 1872 1873 select @rptline = " Transfers Received " + space(5) + 1874 str(@totaltransfers / @NumElapsedSec, 12, 1) + 1875 space(2) + 1876 str(@totaltransfers / convert(real, @NumXacts), 12, 1) + 1877 space(2) + 1878 str(@totaltransfers, 10) + space(5) + 1879 str(100.0 * @totaltransfers / @totalrequested, 5, 1) + @psign 1880 print @rptline 1881 1882 select @rptline = " Data Read from Disk" + space(6) + 1883 str(@totaldiskreads / @NumElapsedSec, 12, 1) + 1884 space(2) + 1885 str(@totaldiskreads / convert(real, @NumXacts), 12, 1) + 1886 space(2) + 1887 str(@totaldiskreads, 10) + space(5) + 1888 str(100.0 * @totaldiskreads / @totalrequested, 5, 1) + @psign 1889 print @rptline 1890 1891 if (@totalrequested - @totalretained) = 0 1892 begin 1893 select @rptline = " Physical Lock Contention" + space(1) + 1894 str(0 / @NumElapsedSec, 12, 1) + 1895 space(2) + 1896 str(0 / convert(real, @NumXacts), 12, 1) + 1897 space(2) + 1898 str(0, 10) + space(5) + 1899 str(0.0, 5, 1) + 1900 @psign 1901 end 1902 else 1903 begin 1904 select @rptline = " Physical Lock Contention" + space(1) + 1905 str(@totalwaits / @NumElapsedSec, 12, 1) + 1906 space(2) + 1907 str(@totalwaits / convert(real, @NumXacts), 12, 1) + 1908 space(2) + 1909 str(@totalwaits, 10) + space(5) + 1910 str(100.0 * @totalwaits / 1911 (@totalrequested - @totalretained), 5, 1) + 1912 @psign 1913 print @rptline 1914 end 1915 1916 if @totallockmgrcalls = 0 1917 begin 1918 select @rptline = " Data Location Efficiency" + space(9) + 1919 " n/a " + 1920 space(9) + 1921 " n/a " + 1922 space(7) + 1923 " n/a " + space(4) + 1924 str(100.0, 5, 1) + @psign 1925 end 1926 else 1927 begin 1928 select @rptline = " Data Location Efficiency" + space(9) + 1929 " n/a " + 1930 space(9) + 1931 " n/a " + 1932 space(7) + 1933 " n/a " + space(4) + 1934 str(100.0 - (100.0 * @totaltransfers / 1935 @totallockmgrcalls), 5, 1) + 1936 @psign 1937 end 1938 1939 print @rptline 1940 end 1941 1942 select @tmp_total = sum(value) 1943 from #tempmonitors 1944 where group_name like "bcmt" and 1945 field_name like "bcmt_pri_req_queued" 1946 1947 if @tmp_total > 0 1948 begin 1949 print @blankline 1950 print " Cluster Cache Manager Requests" 1951 1952 select @rptline = " Total Service Requests " + 1953 str(@tmp_total / @NumElapsedSec, 12, 1) + 1954 space(2) + 1955 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 1956 space(2) + 1957 str(@tmp_total, 10) + space(5) + 1958 str(100.0 * @tmp_total / @tmp_total, 5, 1) + @psign 1959 print @rptline 1960 1961 select @tmp_int = sum(value) 1962 from #tempmonitors 1963 where group_name like "bcmt" and 1964 field_name in ("bcmt_pri_diskwrites", "bcmt_sec_diskwrites") 1965 1966 select @rptline = " Disk Writes " + 1967 str(@tmp_int / @NumElapsedSec, 12, 1) + 1968 space(2) + 1969 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1970 space(2) + 1971 str(@tmp_int, 10) + space(5) + 1972 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1973 print @rptline 1974 1975 select @tmp_int = sum(value) 1976 from #tempmonitors 1977 where group_name like "bcmt" and 1978 field_name like "bcmt_num_of_tx_initiated" 1979 1980 select @rptline = " Transfer Requests " + 1981 str(@tmp_int / @NumElapsedSec, 12, 1) + 1982 space(2) + 1983 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1984 space(2) + 1985 str(@tmp_int, 10) + space(5) + 1986 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 1987 print @rptline 1988 1989 select @tmp_int = sum(value) 1990 from #tempmonitors 1991 where group_name like "bcmt" and 1992 field_name like "bcmt_num_of_downgrades" 1993 1994 select @rptline = " Downgrade Requests " + 1995 str(@tmp_int / @NumElapsedSec, 12, 1) + 1996 space(2) + 1997 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 1998 space(2) + 1999 str(@tmp_int, 10) + space(5) + 2000 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 2001 print @rptline 2002 2003 select @tmp_int = sum(value) 2004 from #tempmonitors 2005 where group_name like "bcmt" and 2006 field_name in ("bcmt_num_of_downgrades_data_ex_to_pr", 2007 "bcmt_num_of_downgrades_data_ex_to_nl", 2008 "bcmt_num_of_downgrades_data_pr_to_nl") 2009 2010 select @rptline = " Data Page Requests " + 2011 str(@tmp_int / @NumElapsedSec, 12, 1) + 2012 space(2) + 2013 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 2014 space(2) + 2015 str(@tmp_int, 10) + space(5) + 2016 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 2017 print @rptline 2018 2019 select @tmp_int = sum(value) 2020 from #tempmonitors 2021 where group_name like "bcmt" and 2022 field_name in ("bcmt_num_of_downgrades_index_ex_to_pr", 2023 "bcmt_num_of_downgrades_index_ex_to_nl", 2024 "bcmt_num_of_downgrades_index_pr_to_nl") 2025 2026 select @rptline = " Index Page Requests " + 2027 str(@tmp_int / @NumElapsedSec, 12, 1) + 2028 space(2) + 2029 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 2030 space(2) + 2031 str(@tmp_int, 10) + space(5) + 2032 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 2033 print @rptline 2034 2035 select @tmp_int = sum(value) 2036 from #tempmonitors 2037 where group_name like "bcmt" and 2038 field_name in ("bcmt_num_of_downgrades_sysalloc_ex_to_pr", 2039 "bcmt_num_of_downgrades_sysalloc_ex_to_nl", 2040 "bcmt_num_of_downgrades_sysalloc_pr_to_nl") 2041 2042 select @rptline = " Alloc Page Requests " + 2043 str(@tmp_int / @NumElapsedSec, 12, 1) + 2044 space(2) + 2045 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 2046 space(2) + 2047 str(@tmp_int, 10) + space(5) + 2048 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 2049 print @rptline 2050 2051 select @tmp_int = sum(value) 2052 from #tempmonitors 2053 where group_name like "bcmt" and 2054 field_name in ("bcmt_num_of_downgrades_firstoam_ex_to_pr", 2055 "bcmt_num_of_downgrades_firstoam_ex_to_nl", 2056 "bcmt_num_of_downgrades_firstoam_pr_to_nl") 2057 2058 select @rptline = " First OAM Page Requests" + 2059 str(@tmp_int / @NumElapsedSec, 12, 1) + 2060 space(2) + 2061 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 2062 space(2) + 2063 str(@tmp_int, 10) + space(5) + 2064 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 2065 print @rptline 2066 2067 select @tmp_int = sum(value) 2068 from #tempmonitors 2069 where group_name like "bcmt" and 2070 field_name in ("bcmt_num_of_downgrades_nonfirstoam_ex_to_pr", 2071 "bcmt_num_of_downgrades_nonfirstoam_ex_to_nl", 2072 "bcmt_num_of_downgrades_nonfirstoam_pr_to_nl") 2073 2074 select @rptline = " Other OAM Page Requests" + 2075 str(@tmp_int / @NumElapsedSec, 12, 1) + 2076 space(2) + 2077 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 2078 space(2) + 2079 str(@tmp_int, 10) + space(5) + 2080 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 2081 print @rptline 2082 2083 end 2084 2085 select @totalreqs = sum(value) 2086 from #tempmonitors 2087 where group_name like "lock" and 2088 field_name like "clm_physical_lockreqs" 2089 select @totallocalmaster = sum(value) 2090 from #tempmonitors 2091 where group_name like "lock" and 2092 field_name like "clm_physical_local_master" 2093 select @totalwaits = sum(value) 2094 from #tempmonitors 2095 where group_name like "lock" and 2096 field_name like "clm_physical_waited" 2097 select @totalgrants = @totalreqs - @totalwaits 2098 2099 print @blankline 2100 if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */ 2101 begin 2102 select @rptline = " Cluster Lock Requests " + 2103 str(@totalreqs / @NumElapsedSec, 12, 1) + 2104 space(2) + 2105 str(@totalreqs / convert(real, @NumXacts), 12, 1) + 2106 space(2) + 2107 str(@totalreqs, 10) + space(5) + 2108 str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign 2109 print @rptline 2110 select @rptline = " Local Master " + space(6) + 2111 str(@totallocalmaster / @NumElapsedSec, 12, 1) + 2112 space(2) + 2113 str(@totallocalmaster / convert(real, @NumXacts), 12, 1) + 2114 space(2) + 2115 str(@totallocalmaster, 10) + space(5) + 2116 str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign 2117 print @rptline 2118 if @totalreqs != 0 2119 begin 2120 select @rptline = " Lock Granted " + space(5) + 2121 str(@totalgrants / @NumElapsedSec, 12, 1) + 2122 space(2) + 2123 str(@totalgrants / convert(real, @NumXacts), 12, 1) + 2124 space(2) + 2125 str(@totalgrants, 10) + space(5) + 2126 str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign 2127 print @rptline 2128 select @rptline = " Lock Waited " + space(5) + 2129 str(@totalwaits / @NumElapsedSec, 12, 1) + 2130 space(2) + 2131 str(@totalwaits / convert(real, @NumXacts), 12, 1) + 2132 space(2) + 2133 str(@totalwaits, 10) + space(5) + 2134 str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign 2135 print @rptline 2136 end 2137 end 2138 2139 select @totalbastreqs = sum(value) 2140 from #tempmonitors 2141 where group_name like "lock" and 2142 field_name like "clm_physical_bast_requests" 2143 2144 if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */ 2145 begin 2146 select @rptline = " Downgrade Req Recv " + 2147 str(@totalbastreqs / @NumElapsedSec, 12, 1) + 2148 space(11) + 2149 @na_str + 2150 space(2) + 2151 str(@totalbastreqs, 10) + 2152 space(7) + 2153 @na_str 2154 print @rptline 2155 end 2156 2157 print @blankline 2158 print " Logical Locks Summary per sec per xact count %% of total" 2159 print @sum1line 2160 select @totalreqs = sum(value) 2161 from #tempmonitors 2162 where group_name like "lock" and 2163 field_name like "clm_logical_lockreqs" 2164 select @totallocalmaster = sum(value) 2165 from #tempmonitors 2166 where group_name like "lock" and 2167 field_name like "clm_logical_local_master" 2168 select @totalwaits = sum(value) 2169 from #tempmonitors 2170 where group_name like "lock" and 2171 field_name like "clm_logical_waited" 2172 select @totalgrants = @totalreqs - @totalwaits 2173 2174 if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */ 2175 begin 2176 select @rptline = " Cluster Lock Requests " + 2177 str(@totalreqs / @NumElapsedSec, 12, 1) + 2178 space(2) + 2179 str(@totalreqs / convert(real, @NumXacts), 12, 1) + 2180 space(2) + 2181 str(@totalreqs, 10) + space(5) + 2182 str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign 2183 print @rptline 2184 select @rptline = " Local Master " + 2185 str(@totallocalmaster / @NumElapsedSec, 12, 1) + 2186 space(2) + 2187 str(@totallocalmaster / convert(real, @NumXacts), 12, 1) + 2188 space(2) + 2189 str(@totallocalmaster, 10) + space(5) + 2190 str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign 2191 print @rptline 2192 if @totalreqs != 0 2193 begin 2194 select @rptline = " Lock Granted " + space(5) + 2195 str(@totalgrants / @NumElapsedSec, 12, 1) + 2196 space(2) + 2197 str(@totalgrants / convert(real, @NumXacts), 12, 1) + 2198 space(2) + 2199 str(@totalgrants, 10) + space(5) + 2200 str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign 2201 print @rptline 2202 select @rptline = " Lock Waited " + space(5) + 2203 str(@totalwaits / @NumElapsedSec, 12, 1) + 2204 space(2) + 2205 str(@totalwaits / convert(real, @NumXacts), 12, 1) + 2206 space(2) + 2207 str(@totalwaits, 10) + space(5) + 2208 str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign 2209 print @rptline 2210 end 2211 end 2212 2213 select @totalbastreqs = sum(value) 2214 from #tempmonitors 2215 where group_name like "lock" and 2216 field_name like "clm_logical_bast_requests" 2217 2218 if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */ 2219 begin 2220 select @rptline = " Downgrade Req Recv " + 2221 str(@totalbastreqs / @NumElapsedSec, 12, 1) + 2222 space(11) + 2223 @na_str + 2224 space(2) + 2225 str(@totalbastreqs, 10) + 2226 space(7) + 2227 @na_str 2228 print @rptline 2229 end 2230 2231 print @blankline 2232 print " Object Locks Summary per sec per xact count %% of total" 2233 print @sum1line 2234 2235 select @totalreqs = sum(value) 2236 from #tempmonitors 2237 where group_name like "lock" and 2238 field_name like "clm_ocm_lockreqs" 2239 select @totallocalmaster = sum(value) 2240 from #tempmonitors 2241 where group_name like "lock" and 2242 field_name like "clm_ocm_local_master" 2243 select @totalwaits = sum(value) 2244 from #tempmonitors 2245 where group_name like "lock" and 2246 field_name like "clm_ocm_waited" 2247 select @totalgrants = @totalreqs - @totalwaits 2248 2249 if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */ 2250 begin 2251 select @rptline = " Cluster Lock Requests " + 2252 str(@totalreqs / @NumElapsedSec, 12, 1) + 2253 space(2) + 2254 str(@totalreqs / convert(real, @NumXacts), 12, 1) + 2255 space(2) + 2256 str(@totalreqs, 10) + space(5) + 2257 str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign 2258 print @rptline 2259 select @rptline = " Local Master " + 2260 str(@totallocalmaster / @NumElapsedSec, 12, 1) + 2261 space(2) + 2262 str(@totallocalmaster / convert(real, @NumXacts), 12, 1) + 2263 space(2) + 2264 str(@totallocalmaster, 10) + space(5) + 2265 str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign 2266 print @rptline 2267 if @totalreqs != 0 2268 begin 2269 select @rptline = " Lock Granted " + space(5) + 2270 str(@totalgrants / @NumElapsedSec, 12, 1) + 2271 space(2) + 2272 str(@totalgrants / convert(real, @NumXacts), 12, 1) + 2273 space(2) + 2274 str(@totalgrants, 10) + space(5) + 2275 str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign 2276 print @rptline 2277 select @rptline = " Lock Waited " + space(5) + 2278 str(@totalwaits / @NumElapsedSec, 12, 1) + 2279 space(2) + 2280 str(@totalwaits / convert(real, @NumXacts), 12, 1) + 2281 space(2) + 2282 str(@totalwaits, 10) + space(5) + 2283 str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign 2284 print @rptline 2285 end 2286 end 2287 2288 select @totalbastreqs = sum(value) 2289 from #tempmonitors 2290 where group_name like "lock" and 2291 field_name like "clm_ocm_bast_requests" 2292 2293 if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */ 2294 begin 2295 select @rptline = " Downgrade Req Recv " + 2296 str(@totalbastreqs / @NumElapsedSec, 12, 1) + 2297 space(11) + 2298 @na_str + 2299 space(2) + 2300 str(@totalbastreqs, 10) + 2301 space(7) + 2302 @na_str 2303 print @rptline 2304 end 2305 2306 /***************** Tuning Recommendations ************************/ 2307 2308 if @Reco = 'Y' 2309 begin 2310 select @reco_hdr_prn = 0 2311 print @blankline 2312 2313 select @deadlocks = value from #tempmonitors 2314 where group_name = "lock" 2315 and field_name = "deadlocks" 2316 2317 select @deadlock_search = value from #tempmonitors 2318 where group_name = "lock" 2319 and field_name = "deadlock_search" 2320 2321 /* 2322 ** If the number of deadlocks is 0 and the 2323 ** number of times deadlock search was done 2324 ** is greater than 0, consider increasing the 2325 ** 'deadlock checking period' parameter 2326 */ 2327 if (@deadlocks = 0 and @deadlock_search > 0) 2328 begin 2329 if (@reco_hdr_prn = 0) 2330 begin 2331 print @recotxt 2332 print @recoline 2333 select @reco_hdr_prn = 1 2334 end 2335 print " - Consider increasing the 'deadlock checking period' parameter" 2336 print " by 50 ms." 2337 print @blankline 2338 end 2339 /* 2340 ** If the number of deadlocks is greated than 0 and the 2341 ** number of times deadlock search was done is 0, 2342 ** consider decreasing the 'deadlock checking period' parameter 2343 */ 2344 else if (@deadlocks > 0 and @deadlock_search = 0) 2345 begin 2346 if (@reco_hdr_prn = 0) 2347 begin 2348 print @recotxt 2349 print @recoline 2350 select @reco_hdr_prn = 1 2351 end 2352 print " - Consider decreasing the 'deadlock checking period' parameter" 2353 print " by 50 ms." 2354 print @blankline 2355 end 2356 2357 /* 2358 ** If the percentage of waits on the last page 2359 ** is > 10% consider partitioning the table or 2360 ** increasing the number of partitions if the 2361 ** table is already partitioned 2362 */ 2363 if (@reco_lastpg_wait_percent > 10) 2364 begin 2365 if (@reco_hdr_prn = 0) 2366 begin 2367 print @recotxt 2368 print @recoline 2369 select @reco_hdr_prn = 1 2370 end 2371 print " - Consider partitioning the heap tables or increasing the" 2372 print " number of partitions on already partitioned heap tables." 2373 print @blankline 2374 end 2375 2376 /* 2377 ** If the average length of overflow chain for page & row lock 2378 ** hashtable is > 4 consider increasing the 'lock hashtable size' 2379 ** to the next power of 2 from the current configured value. 2380 */ 2381 if (@reco_avg_chain_length_pagerow > 4) 2382 begin 2383 if (@reco_hdr_prn = 0) 2384 begin 2385 print @recotxt 2386 print @recoline 2387 select @reco_hdr_prn = 1 2388 end 2389 print " - Consider increasing the value of 'lock hashtable size' " 2390 print " configuration option. The value for this configuration " 2391 print " option must be a power of 2." 2392 print @blankline 2393 end 2394 2395 /* 2396 ** If the number of engines is > 1 report recommendation for lock 2397 ** manager related spinlock contentions. 2398 */ 2399 if (@NumEngines > 1) 2400 begin /* { */ 2401 /* 2402 ** if the contention on the fglock hashtable spinlocks 2403 ** is >= 10%, consider decreasing the 'lock spinlock ratio' 2404 ** config option. 2405 */ 2406 if (@reco_fgspin_contention >= 10) 2407 begin 2408 if (@reco_hdr_prn = 0) 2409 begin /* { */ 2410 print @recotxt 2411 print @recoline 2412 select @reco_hdr_prn = 1 2413 end /* } */ 2414 print " - Consider reducing the 'lock spinlock ratio'." 2415 print @blankline 2416 end 2417 /* 2418 ** if the contention on the table lock hashtable spinlocks 2419 ** is >= 10%, consider decreasing the 'lock table spinlock 2420 ** ratio' config option. 2421 */ 2422 if (@reco_tabspin_contention >= 10) 2423 begin 2424 if (@reco_hdr_prn = 0) 2425 begin /* { */ 2426 print @recotxt 2427 print @recoline 2428 select @reco_hdr_prn = 1 2429 end /* } */ 2430 print " - Consider reducing the 'lock table spinlock ratio'." 2431 print @blankline 2432 end 2433 /* 2434 ** if the contention on the address lock hashtable spinlocks 2435 ** is >= 10%, consider decreasing the 'lock address spinlock 2436 ** ratio' config option. 2437 */ 2438 if (@reco_addrspin_contention >= 10) 2439 begin 2440 if (@reco_hdr_prn = 0) 2441 begin /* { */ 2442 print @recotxt 2443 print @recoline 2444 select @reco_hdr_prn = 1 2445 end /* } */ 2446 print " - Consider reducing the 'lock address spinlock ratio'." 2447 print @blankline 2448 end 2449 end /* } */ 2450 end 2451 print @blankline 2452 return 0 2453
exec sp_procxmode 'sp_sysmon_locks', 'AnyMode' go Grant Execute on sp_sysmon_locks to public go
DEPENDENCIES |
PROCS AND TABLES USED read_writes table tempdb..#foo (1) reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze ![]() called by proc sybsystemprocs..sp_sysmon ![]() |