Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_xactmgmt | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** log activity including activity in the user log caches. 4 */ 5 create procedure sp_sysmon_xactmgmt 6 7 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 8 @NumXacts int, /* for per transactions calculations */ 9 @Reco char(1) /* Flag for recommendations */ 10 as 11 12 /* --------- declare local variables --------- */ 13 declare @tmp_int int /* temp var for integer storage */ 14 declare @tmp_int2 int /* temp var for integer storage */ 15 declare @tmp_total int /* temp var for summing 'total #s' data */ 16 declare @sum1line char(80) /* string to delimit total lines without 17 ** percent calc on printout */ 18 declare @sum2line char(67) /* string to delimit total lines with 19 ** percent calc on printout */ 20 declare @blankline char(1) /* to print blank line */ 21 declare @psign char(3) /* hold a percent sign (%) for print out */ 22 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 23 declare @rptline char(80) /* formatted stats line for print statement */ 24 declare @section char(80) /* string to delimit sections on printout */ 25 declare @discarded_subcmd int /* # of ML-DML subcmds successfully discarded */ 26 declare @logged_subcmd int /* # of ML-DML subcmds which could not be 27 ** discarded and therefore had to be logged */ 28 declare @fldml_plc_flush_full int /* PLC flushes due to full PLC accounted for 29 ** Fully Logged DML commands. */ 30 declare @mldml_plc_flush_full int /* PLC flushes due to full PLC accounted for 31 ** Minimally Logged DML commands. */ 32 declare @fldml_plc_flush_slr int /* PLC flushes due to SLR logging accounted 33 ** for Fully Logged DML commands. */ 34 declare @mldml_plc_flush_slr int /* PLC flushes due to SLR logging accounted 35 ** for Minimally Logged DML commands. */ 36 37 38 /* ------------- Variables for Tuning Recommendations ------------*/ 39 declare @recotxt char(80) /* Header for tuning recommendation */ 40 declare @recoline char(80) /* to underline recotxt */ 41 declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */ 42 declare @reco_by_slr real 43 declare @reco_by_full_ulc real 44 45 /* --------- Setup Environment --------- */ 46 set nocount on /* disable row counts being sent to client */ 47 48 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 49 select @sum2line = " ------------------------- ------------ ------------ ----------" 50 select @blankline = " " 51 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 52 select @na_str = "n/a" 53 select @section = "===============================================================================" 54 55 56 print @section 57 print @blankline 58 59 print "Transaction Management" 60 print "----------------------" 61 print @blankline 62 print " ULC Flushes to Xact Log per sec per xact count %% of total" 63 print @sum1line 64 65 select @tmp_total = SUM(value) 66 from #tempmonitors 67 where group_name = 'xls' and 68 (field_name like "plc_flush_%" or 69 field_name like "mldml_plc_flush_%") and 70 field_name != "plc_flush_discard" and 71 field_name != "mldml_plc_discard" 72 73 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 74 begin 75 select @rptline = " Total ULC Flushes 0.0 0.0 0 n/a" 76 print @rptline 77 end 78 else 79 begin 80 print " Any Logging Mode DMLs" 81 82 select @tmp_int = value 83 from #tempmonitors 84 where group_name = 'xls' and 85 field_name = "plc_flush_endxact" 86 87 select @rptline = " by End Transaction" + space(7) + 88 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 89 space(2) + 90 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 91 space(2) + 92 str(@tmp_int, 10) + space(5) + 93 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 94 @psign 95 print @rptline 96 97 select @tmp_int = value 98 from #tempmonitors 99 where group_name = 'xls' and 100 field_name = "plc_flush_xdeschange" 101 102 select @rptline = " by Change of Database" + space(4) + 103 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 104 space(2) + 105 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 106 space(2) + 107 str(@tmp_int, 10) + space(5) + 108 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 109 @psign 110 print @rptline 111 112 select @tmp_int = value 113 from #tempmonitors 114 where group_name = 'xls' and 115 field_name = "plc_flush_unpin" 116 117 select @rptline = " by Unpin" + space(17) + 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 / @tmp_total, 5, 1) + 124 @psign 125 print @rptline 126 127 select @tmp_int = SUM(value) 128 from #tempmonitors 129 where group_name = 'xls' and field_name IN 130 ("plc_flush_pmscan") 131 132 select @rptline = " by Log markers" + space(11) + 133 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 134 space(2) + 135 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 136 space(2) + 137 str(@tmp_int, 10) + space(5) + 138 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 139 @psign 140 print @rptline 141 print @blankline 142 143 print " Fully Logged DMLs" 144 145 select @tmp_int = value 146 from #tempmonitors 147 where group_name = 'xls' and 148 field_name = "plc_flush_full" 149 150 select @fldml_plc_flush_full = @tmp_int 151 152 select @rptline = " by Full ULC" + space(14) + 153 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 154 space(2) + 155 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 156 space(2) + 157 str(@tmp_int, 10) + space(5) + 158 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 159 @psign 160 print @rptline 161 162 select @tmp_int = value 163 from #tempmonitors 164 where group_name = 'xls' and 165 field_name = "plc_flush_slr_xact" 166 167 select @fldml_plc_flush_slr = @tmp_int 168 169 select @rptline = " by Single Log Record" + space(5) + 170 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 171 space(2) + 172 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 173 space(2) + 174 str(@tmp_int, 10) + space(5) + 175 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 176 @psign 177 print @rptline 178 179 print @blankline 180 print " Minimally Logged DMLs" 181 182 select @tmp_int = value 183 from #tempmonitors 184 where group_name = 'xls' and 185 field_name = "mldml_plc_flush_full" 186 187 select @mldml_plc_flush_full = @tmp_int 188 189 select @rptline = " by Full ULC" + space(14) + 190 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 191 space(2) + 192 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 193 space(2) + 194 str(@tmp_int, 10) + space(5) + 195 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 196 @psign 197 print @rptline 198 199 select @reco_by_full_ulc = convert(int, 100.0 * 200 ((1.0 * (@fldml_plc_flush_full + @mldml_plc_flush_full)) 201 / @tmp_total)) 202 203 select @tmp_int = value 204 from #tempmonitors 205 where group_name = 'xls' and 206 field_name = "mldml_plc_flush_slr_xact" 207 208 select @mldml_plc_flush_slr = @tmp_int 209 210 select @rptline = " by Single Log Record" + space(5) + 211 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 212 space(2) + 213 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 214 space(2) + 215 str(@tmp_int, 10) + space(5) + 216 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 217 @psign 218 print @rptline 219 220 select @reco_by_slr = convert(int, 100.0 * 221 ((1.0 * (@fldml_plc_flush_slr + @mldml_plc_flush_slr)) 222 / @tmp_total)) 223 224 select @tmp_int = value 225 from #tempmonitors 226 where group_name = 'xls' and 227 field_name = "mldml_plc_flush_beginsubcmd" 228 229 select @rptline = " by Start of Sub-Command" + space(2) + 230 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 231 space(2) + 232 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 233 space(2) + 234 str(@tmp_int, 10) + space(5) + 235 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 236 @psign 237 print @rptline 238 239 select @tmp_int = value 240 from #tempmonitors 241 where group_name = 'xls' and 242 field_name = "mldml_plc_flush_endsubcmd" 243 244 select @logged_subcmd = @tmp_int 245 246 select @rptline = " by End of Sub-Command" + space(4) + 247 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 248 space(2) + 249 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 250 space(2) + 251 str(@tmp_int, 10) + space(5) + 252 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 253 @psign 254 print @rptline 255 print @sum2line 256 select @rptline = " Total ULC Flushes" + space(10) + 257 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 258 space(2) + 259 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 260 space(2) + 261 str(@tmp_total, 10) 262 print @rptline 263 end 264 265 print @blankline 266 print " ULC Flushes Skipped per sec per xact count %% of total" 267 print @sum1line 268 269 select @tmp_total = SUM(value) 270 from #tempmonitors 271 where group_name = 'xls' and 272 field_name in ("plc_flush_discard", "mldml_plc_discard") 273 274 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 275 begin 276 select @rptline = " Total ULC Skips 0.0 0.0 0 n/a" 277 print @rptline 278 end 279 else 280 begin 281 print " Fully Logged DMLs" 282 select @tmp_int = value 283 from #tempmonitors 284 where group_name = 'xls' and 285 field_name = "plc_flush_discard" 286 287 select @rptline = " by ULC Discards" + space(10) + 288 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 289 space(2) + 290 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 291 space(2) + 292 str(@tmp_int, 10) + space(5) + 293 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 294 @psign 295 print @rptline 296 297 print " Minimally Logged DMLs" 298 select @tmp_int = value 299 from #tempmonitors 300 where group_name = 'xls' and 301 field_name = "mldml_plc_discard" 302 303 select @discarded_subcmd = value 304 from #tempmonitors 305 where group_name = 'xls' and 306 field_name = "mldml_subcmd_discard" 307 308 select @rptline = " by ULC Discards" + space(10) + 309 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 310 space(2) + 311 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 312 space(2) + 313 str(@tmp_int, 10) + space(5) + 314 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 315 @psign 316 print @rptline 317 318 print @sum2line 319 select @rptline = " Total ULC Flushes Skips" + space(4) + 320 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 321 space(2) + 322 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 323 space(2) + 324 str(@tmp_total, 10) 325 print @rptline 326 327 end 328 329 print @blankline 330 print " ULC Log Records per sec per xact count %% of total" 331 print @sum1line 332 333 select @tmp_total = SUM(value) 334 from #tempmonitors 335 where group_name = 'xls' and 336 field_name in ("plc_logrecs", "mldml_plc_logrecs") 337 338 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 339 begin 340 select @rptline = " Total ULC Log Records 0.0 0.0 0 n/a" 341 print @rptline 342 end 343 else 344 begin 345 select @tmp_int = value 346 from #tempmonitors 347 where group_name = 'xls' and 348 field_name = "plc_logrecs" 349 350 select @rptline = " Fully Logged DMLs" + space(10) + 351 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 352 space(2) + 353 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 354 space(2) + 355 str(@tmp_int, 10) + space(7) + 356 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 357 @psign 358 print @rptline 359 360 select @tmp_int = value 361 from #tempmonitors 362 where group_name = 'xls' and 363 field_name = "mldml_plc_logrecs" 364 365 select @rptline = " Minimally Logged DMLs" + space(6) + 366 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 367 space(2) + 368 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 369 space(2) + 370 str(@tmp_int, 10) + space(7) + 371 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 372 @psign 373 print @rptline 374 375 print @sum2line 376 select @rptline = " Total ULC Log Records" + space(6) + 377 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 378 space(2) + 379 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 380 space(2) + 381 str(@tmp_total, 10) 382 print @rptline 383 end 384 385 print @blankline 386 print " Max ULC Size During Sample" 387 print " --------------------------" 388 389 select @tmp_int = value 390 from #tempmonitors 391 where group_name = 'xls' and 392 field_name = "plc_maxused" 393 394 select @rptline = " Fully Logged DMLs" + space(19) + 395 @na_str + space(11) + 396 @na_str + space(2) + 397 str(@tmp_int, 10) + space(7) + 398 @na_str 399 print @rptline 400 401 select @tmp_int = value 402 from #tempmonitors 403 where group_name = 'xls' and 404 field_name = "mldml_plc_maxused" 405 406 select @rptline = " Minimally Logged DMLs" + space(15) + 407 @na_str + space(11) + 408 @na_str + space(2) + 409 str(@tmp_int, 10) + space(7) + 410 @na_str 411 print @rptline 412 print @blankline 413 414 print " ML-DMLs Sub-Command Scans per sec per xact count %% of total" 415 print @sum1line 416 417 select @tmp_total = SUM(value) 418 from #tempmonitors 419 where group_name = 'xls' and 420 field_name in ("mldml_subcmd_plc_scan", 421 "mldml_subcmd_syslogs_scan") 422 423 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 424 begin 425 select @rptline = " Total Sub-Command Scans 0.0 0.0 0 n/a" 426 print @rptline 427 end 428 else 429 begin 430 select @tmp_int = value 431 from #tempmonitors 432 where group_name = 'xls' and 433 field_name = "mldml_subcmd_plc_scan" 434 435 select @rptline = " ULC Scans" + space(18) + 436 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 437 space(2) + 438 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 439 space(2) + 440 str(@tmp_int, 10) + space(7) + 441 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 442 @psign 443 print @rptline 444 445 select @tmp_int = value 446 from #tempmonitors 447 where group_name = 'xls' and 448 field_name = "mldml_subcmd_syslogs_scan" 449 450 select @rptline = " Syslogs Scans" + space(14) + 451 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 452 space(2) + 453 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 454 space(2) + 455 str(@tmp_int, 10) + space(7) + 456 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 457 @psign 458 print @rptline 459 460 print @sum2line 461 select @rptline = " Total Sub-Command Scans" + space(4) + 462 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 463 space(2) + 464 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 465 space(2) + 466 str(@tmp_total, 10) 467 print @rptline 468 end 469 470 print @blankline 471 print " ML-DMLs ULC Efficiency per sec per xact count %% of total" 472 print @sum1line 473 474 select @tmp_total = @discarded_subcmd + @logged_subcmd 475 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 476 begin 477 select @rptline = " Total ML-DML Sub-Commands 0.0 0.0 0 n/a" 478 print @rptline 479 end 480 else 481 begin 482 select @tmp_int = @discarded_subcmd 483 484 select @rptline = " Discarded Sub-Commands" + space(5) + 485 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 486 space(2) + 487 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 488 space(2) + 489 str(@tmp_int, 10) + space(7) + 490 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 491 @psign 492 print @rptline 493 494 select @tmp_int = @logged_subcmd 495 496 select @rptline = " Logged Sub-Commands" + space(8) + 497 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 498 space(2) + 499 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 500 space(2) + 501 str(@tmp_int, 10) + space(7) + 502 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 503 @psign 504 print @rptline 505 506 print @sum2line 507 select @rptline = " Total ML-DML Sub-Commands" + space(2) + 508 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 509 space(2) + 510 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 511 space(2) + 512 str(@tmp_total, 10) 513 print @rptline 514 end 515 516 print @blankline 517 print " ULC Semaphore Requests" 518 519 select @tmp_total = value 520 from #tempmonitors 521 where group_name = 'xls' and 522 field_name = "plc_lock_calls" 523 524 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 525 begin 526 select @rptline = " Total ULC Semaphore Req 0.0 0.0 0 n/a" 527 print @rptline 528 end 529 else 530 begin 531 select @tmp_int2 = value 532 from #tempmonitors 533 where group_name = 'xls' and 534 field_name = "plc_lock_waits" 535 536 /* calculate "immediately granted" spinlocks */ 537 select @tmp_int = @tmp_total - @tmp_int2 538 539 540 select @rptline = " Granted" + space(18) + 541 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 542 space(2) + 543 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 544 space(2) + 545 str(@tmp_int, 10) + space(5) + 546 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 547 @psign 548 print @rptline 549 550 select @rptline = " Waited" + space(19) + 551 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) + 552 space(2) + 553 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) + 554 space(2) + 555 str(@tmp_int2, 10) + space(5) + 556 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + 557 @psign 558 print @rptline 559 560 print @sum2line 561 select @rptline = " Total ULC Semaphore Req" + space(4) + 562 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 563 space(2) + 564 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 565 space(2) + 566 str(@tmp_total, 10) 567 568 569 print @rptline 570 end /* else @tmp_total != 0 */ 571 572 print @blankline 573 574 print " Log Semaphore Requests" 575 576 select @tmp_total = SUM(value) 577 from #tempmonitors 578 where group_name = 'xls' and 579 field_name IN ("log_lock_granted", "log_lock_waited", "log_objectlock_needwait") 580 581 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 582 begin 583 select @rptline = " Total Log Semaphore Req 0.0 0.0 0 n/a" 584 print @rptline 585 end 586 else 587 begin 588 select @tmp_int = value 589 from #tempmonitors 590 where group_name = 'xls' and 591 field_name = "log_lock_granted" 592 593 select @rptline = " Granted" + space(18) + 594 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 595 space(2) + 596 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 597 space(2) + 598 str(@tmp_int, 10) + space(5) + 599 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 600 print @rptline 601 602 select @tmp_int = value 603 from #tempmonitors 604 where group_name = 'xls' and 605 field_name = "log_lock_waited" 606 607 select @rptline = " Local Waited" + space(13) + 608 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 609 space(2) + 610 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 611 space(2) + 612 str(@tmp_int, 10) + space(5) + 613 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 614 @psign 615 print @rptline 616 617 select @tmp_int = value 618 from #tempmonitors 619 where group_name = 'xls' and 620 field_name = "log_objectlock_needwait" 621 622 select @rptline = " Global Waited" + space(12) + 623 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 624 space(2) + 625 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 626 space(2) + 627 str(@tmp_int, 10) + space(5) + 628 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 629 @psign 630 print @rptline 631 632 print @sum2line 633 select @rptline = " Total Log Semaphore Req" + space(4) + 634 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 635 space(2) + 636 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 637 space(2) + 638 str(@tmp_total, 10) 639 print @rptline 640 end 641 642 print @blankline 643 644 645 646 select @tmp_int = SUM(value) 647 from #tempmonitors 648 where group_name like "buffer_%" and 649 field_name = "last_log_page_writes" 650 651 select @tmp_int = SUM(value) 652 from #tempmonitors 653 where group_name like "buffer_%" and 654 field_name = "log_page_writes" 655 656 select @rptline = " Transaction Log Writes" + space(5) + 657 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 658 space(2) + 659 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 660 space(2) + 661 str(@tmp_int, 10) + space(7) + 662 @na_str 663 print @rptline 664 665 select @tmp_int2 = value 666 from #tempmonitors 667 where group_name = "access" and 668 field_name = "log_page_allocations" 669 670 select @rptline = " Transaction Log Alloc" + space(6) + 671 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) + 672 space(2) + 673 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) + 674 space(2) + 675 str(@tmp_int2, 10) + space(7) + 676 @na_str 677 print @rptline 678 679 if @tmp_int2 != 0 /* avoid divide by zero errors */ 680 begin 681 select @rptline = " Avg # Writes per Log Page" + space(11) + 682 @na_str + space(11) + 683 @na_str + space(2) + 684 str(convert(real, @tmp_int) / @tmp_int2, 10, 5) + space(7) + 685 @na_str 686 print @rptline 687 end 688 689 print @blankline 690 691 if @Reco = 'Y' 692 begin 693 select @recotxt = " Tuning Recommendations for Transaction Management" 694 select @recoline = " -------------------------------------------------" 695 select @reco_hdr_prn = 0 696 697 /* 698 ** If the % of flushes on account of ULC being full is > 20% 699 ** consider increasing the 'user log cache size' 700 */ 701 if @reco_by_full_ulc > 20 702 begin 703 if (@reco_hdr_prn = 0) 704 begin 705 print @recotxt 706 print @recoline 707 select @reco_hdr_prn = 1 708 end 709 710 print " - Consider increasing the 'user log cache size'" 711 print " configuration parameter." 712 print @blankline 713 select @reco_hdr_prn = 1 714 end 715 716 /* 717 ** If the % of flushes on account of SLR is > 20% 718 ** and the % of flushes on account of ULC being full 719 ** is < 20% consider decreasing the user log cache size 720 */ 721 if @reco_by_slr > 20 and @reco_by_full_ulc < 20 722 begin 723 if (@reco_hdr_prn = 0) 724 begin 725 print @recotxt 726 print @recoline 727 select @reco_hdr_prn = 1 728 end 729 730 print " - Consider decreasing the 'user log cache size'" 731 print " configuration parameter if it is greater than the" 732 print " logical database page size." 733 print @blankline 734 select @reco_hdr_prn = 1 735 end 736 end 737 738 return 0 739
exec sp_procxmode 'sp_sysmon_xactmgmt', 'AnyMode' go Grant Execute on sp_sysmon_xactmgmt to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze called by proc sybsystemprocs..sp_sysmon |