Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_xactsum | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** the type of activites performed by transactions running during 4 ** the sampling period, including the number of transactions and 5 ** a summary of insert, update and delete activity. 6 ** 7 */ 8 create procedure sp_sysmon_xactsum 9 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 10 @NumXacts int /* for per transactions calculations */ 11 as 12 13 /* --------- declare local variables --------- */ 14 declare @NumXactOps int /* total # of rows affected by 15 ** inserts/updates/deletes */ 16 declare @tmp_int int /* temp var for integer storage */ 17 declare @tmp_int2 int /* temp var for integer storage */ 18 declare @tmp_total int /* temp var for summing 'total #s' data */ 19 declare @sum1line char(80) /* string to delimit total lines without 20 ** percent calc on printout */ 21 declare @sum2line char(67) /* string to delimit total lines with percent 22 ** calc on printout */ 23 declare @sum3line char(67) /* string to delimit total lines with percent 24 ** calc on printout */ 25 declare @blankline char(1) /* to print blank line */ 26 declare @psign char(3) /* hold a percent sign (%) for print out */ 27 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 28 declare @rptline char(80) /* formatted stats line for print statement */ 29 declare @section char(80) /* string to delimit sections on printout */ 30 /* --------- Setup Environment --------- */ 31 set nocount on /* disable row counts being sent to client */ 32 33 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 34 select @sum2line = " ------------------------- ------------ ------------ ----------" 35 select @sum3line = " ========================= ============ ============ ==========" 36 select @blankline = " " 37 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 38 select @na_str = "n/a" 39 select @section = "===============================================================================" 40 41 /* ================ Transaction Profile Section ================= */ 42 print @section 43 print @blankline 44 print "Transaction Profile" 45 print "-------------------" 46 print @blankline 47 48 /* -------- transaction summary ------------*/ 49 print " Transaction Summary per sec per xact count %% of total" 50 print @sum1line 51 52 select @rptline = " Committed Xacts" + space(10) + 53 str(@NumXacts / (@NumElapsedMs / 1000.0), 12, 1) + 54 space(11) + @na_str + space(2) + 55 str(@NumXacts, 10) + space(5) + @na_str 56 print @rptline 57 print @blankline 58 59 /* 60 ** transaction detail 61 */ 62 63 print " Transaction Detail per sec per xact count %% of total" 64 print @sum1line 65 66 /* 67 ** get total number of Transaction Operations for percentage 68 ** calculations on total lines 69 */ 70 select @NumXactOps = SUM(value) 71 from #tempmonitors 72 where (group_name = 'access' and field_name IN 73 ("ncinsert", "cinsert", "deferred_update", 74 "direct_inplace_update", "direct_notinplace_update", 75 "direct_expensive_update", "delete", "bulk_fast_insert", 76 "mldml_ncinsert", "mldml_cinsert", 77 "mldml_direct_inplace_update", 78 "mldml_direct_notinplace_update", 79 "mldml_direct_expensive_update", "mldml_delete")) 80 OR (group_name = 'dolaccess' and field_name IN 81 ("dolinsert", "dolupdates", "doldelete_total", 82 "mldml_dolinsert", "mldml_dolupdates", 83 "mldml_doldelete_total")) 84 85 if @NumXactOps = 0 /* Avoid Divide by Zero Errors */ 86 begin 87 select @rptline = " Total Rows Affected 0.0 0.0 0 n/a" 88 print @rptline 89 end 90 else 91 begin 92 print " Inserts" 93 94 select @tmp_total = SUM(value) 95 from #tempmonitors 96 where (group_name = 'access' and 97 field_name IN ("ncinsert", "cinsert", 98 "bulk_fast_insert", "mldml_ncinsert", 99 "mldml_cinsert")) 100 or (group_name = 'dolaccess' and 101 field_name IN ("dolinsert", "mldml_dolinsert")) 102 103 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 104 begin 105 select @rptline = " Total Rows Inserted 0.0 0.0 0 n/a" 106 print @rptline 107 end 108 else 109 begin 110 111 print " Fully Logged" 112 select @tmp_int = value 113 from #tempmonitors 114 where group_name = 'access' and 115 field_name = "ncinsert" 116 117 select @rptline = " APL Heap Table" + space(9) + 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) + @psign 124 print @rptline 125 126 select @tmp_int = value 127 from #tempmonitors 128 where group_name = 'access' and 129 field_name = "cinsert" 130 131 select @rptline = " APL Clustered Table" + space(4) + 132 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 133 space(2) + 134 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 135 space(2) + 136 str(@tmp_int, 10) + space(5) + 137 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 138 print @rptline 139 140 select @tmp_int = value 141 from #tempmonitors 142 where group_name = 'dolaccess' and 143 field_name = "dolinsert" 144 145 select @rptline = " Data Only Lock Table" + space(3) + 146 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 147 space(2) + 148 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 149 space(2) + 150 str(@tmp_int, 10) + space(5) + 151 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 152 print @rptline 153 154 select @tmp_int = value 155 from #tempmonitors 156 where group_name = 'access' and 157 field_name = "bulk_fast_insert" 158 159 select @rptline = " Fast Bulk Insert" + space(7) + 160 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 161 space(2) + 162 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 163 space(2) + 164 str(@tmp_int, 10) + space(5) + 165 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 166 print @rptline 167 168 print " Minimally Logged" 169 select @tmp_int = value 170 from #tempmonitors 171 where group_name = 'access' and 172 field_name = "mldml_ncinsert" 173 174 select @rptline = " APL Heap Table" + space(9) + 175 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 176 space(2) + 177 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 178 space(2) + 179 str(@tmp_int, 10) + space(5) + 180 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 181 print @rptline 182 183 select @tmp_int = value 184 from #tempmonitors 185 where group_name = 'access' and 186 field_name = "mldml_cinsert" 187 188 select @rptline = " APL Clustered Table" + space(4) + 189 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 190 space(2) + 191 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 192 space(2) + 193 str(@tmp_int, 10) + space(5) + 194 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 195 print @rptline 196 197 select @tmp_int = value 198 from #tempmonitors 199 where group_name = 'dolaccess' and 200 field_name = "mldml_dolinsert" 201 202 select @rptline = " Data Only Lock Table" + space(3) + 203 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 204 space(2) + 205 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 206 space(2) + 207 str(@tmp_int, 10) + space(5) + 208 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 209 print @rptline 210 211 end /* else */ 212 213 print @sum1line 214 select @rptline = " Total Rows Inserted" + space(8) + 215 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 216 space(2) + 217 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 218 space(2) + 219 str(@tmp_total, 10) + space(5) + 220 str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign 221 print @rptline 222 print @blankline 223 224 print " Updates" 225 226 select @tmp_total = SUM(value) 227 from #tempmonitors 228 where (group_name = 'access' and field_name IN 229 ("deferred_update", "direct_inplace_update", 230 "direct_notinplace_update", "direct_expensive_update", 231 "mldml_deferred_update", "mldml_direct_inplace_update", 232 "mldml_direct_notinplace_update", 233 "mldml_direct_expensive_update")) 234 OR (group_name = 'dolaccess' and field_name IN ("dolupdates", 235 "mldml_dolupdates")) 236 237 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 238 begin 239 select @rptline = " Total Rows Updated 0.0 0.0 0 n/a" 240 print @rptline 241 end 242 else 243 begin 244 245 print " Fully Logged" 246 select @tmp_int = value 247 from #tempmonitors 248 where group_name = 'access' and 249 field_name = "deferred_update" 250 251 select @rptline = " APL Deferred" + space(11) + 252 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 253 space(2) + 254 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 255 space(2) + 256 str(@tmp_int, 10) + space(5) + 257 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 258 print @rptline 259 260 select @tmp_int = value 261 from #tempmonitors 262 where group_name = 'access' and 263 field_name = "direct_inplace_update" 264 265 select @rptline = " APL Direct In-place" + space(4) + 266 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 267 space(2) + 268 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 269 space(2) + 270 str(@tmp_int, 10) + space(5) + 271 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 272 print @rptline 273 274 select @tmp_int = value 275 from #tempmonitors 276 where group_name = 'access' and 277 field_name = "direct_notinplace_update" 278 279 select @rptline = " APL Direct Cheap" + space(7) + 280 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 281 space(2) + 282 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 283 space(2) + 284 str(@tmp_int, 10) + space(5) + 285 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 286 print @rptline 287 288 select @tmp_int = value 289 from #tempmonitors 290 where group_name = 'access' and 291 field_name = "direct_expensive_update" 292 293 select @rptline = " APL Direct Expensive" + space(3) + 294 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 295 space(2) + 296 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 297 space(2) + 298 str(@tmp_int, 10) + space(5) + 299 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 300 print @rptline 301 302 select @tmp_int2 = value 303 from #tempmonitors 304 where group_name = 'dolaccess' and 305 field_name = "dolupdate_deferred" 306 307 select @rptline = " DOL Deferred" + space(11) + 308 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) + 309 space(2) + 310 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) + 311 space(2) + 312 str(@tmp_int2, 10) + space(5) + 313 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign 314 print @rptline 315 316 select @tmp_int = value - @tmp_int2 317 from #tempmonitors 318 where group_name = 'dolaccess' and 319 field_name = "dolupdates" 320 321 select @rptline = " DOL Direct" + space(13) + 322 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 323 space(2) + 324 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 325 space(2) + 326 str(@tmp_int, 10) + space(5) + 327 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 328 print @rptline 329 330 print " Minimally Logged" 331 select @tmp_int = value 332 from #tempmonitors 333 where group_name = 'access' and 334 field_name = "mldml_direct_inplace_update" 335 336 select @rptline = " APL Direct In-place" + space(4) + 337 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 338 space(2) + 339 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 340 space(2) + 341 str(@tmp_int, 10) + space(5) + 342 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 343 print @rptline 344 345 select @tmp_int = value 346 from #tempmonitors 347 where group_name = 'access' and 348 field_name = "mldml_direct_notinplace_update" 349 350 select @rptline = " APL Direct Cheap" + space(7) + 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(5) + 356 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 357 print @rptline 358 359 select @tmp_int = value 360 from #tempmonitors 361 where group_name = 'access' and 362 field_name = "mldml_direct_expensive_update" 363 364 select @rptline = " APL Direct Expensive" + space(3) + 365 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 366 space(2) + 367 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 368 space(2) + 369 str(@tmp_int, 10) + space(5) + 370 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 371 print @rptline 372 373 374 select @tmp_int = value 375 from #tempmonitors 376 where group_name = 'dolaccess' and 377 field_name = "mldml_dolupdates" 378 379 select @rptline = " DOL Direct" + space(13) + 380 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 381 space(2) + 382 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 383 space(2) + 384 str(@tmp_int, 10) + space(5) + 385 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 386 print @rptline 387 388 end 389 390 print @sum1line 391 select @rptline = " Total Rows Updated" + space(9) + 392 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 393 space(2) + 394 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 395 space(2) + 396 str(@tmp_total, 10) + space(5) + 397 str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign 398 print @rptline 399 print @blankline 400 401 402 print " Data Only Locked Updates" 403 404 select @tmp_total = SUM(value) 405 from #tempmonitors 406 where group_name = 'dolaccess' and field_name IN 407 ("dolupdate_replace", "mldml_dolupdate_shrink", 408 "dolupdate_expand_incfs", "mldml_dolupdate_expand_shift", 409 "dolupdate_expand_after_gc", 410 "mldml_dolupdate_forward_firstlvl", 411 "dolupdate_forward_secondlvl", 412 "mldml_dolupdate_migrate_rowhome", 413 "mldml_dolupdate_replace", "mldml_dolupdate_shrink", 414 "mldml_dolupdate_expand_incfs", "mldml_dolupdate_expand_shift", 415 "mldml_dolupdate_expand_after_gc", 416 "mldml_dolupdate_forward_firstlvl", 417 "mldml_dolupdate_forward_secondlvl", 418 "mldml_dolupdate_migrate_rowhome") 419 420 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 421 begin 422 select @rptline = " Total Rows Updated 0.0 0.0 0 n/a" 423 print @rptline 424 end 425 else 426 begin 427 428 print " Fully Logged" 429 select @tmp_int = value 430 from #tempmonitors 431 where group_name = 'dolaccess' and 432 field_name = "dolupdate_replace" 433 434 select @rptline = " DOL Replace" + space(12) + 435 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 436 space(2) + 437 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 438 space(2) + 439 str(@tmp_int, 10) + space(5) + 440 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 441 print @rptline 442 443 select @tmp_int = value 444 from #tempmonitors 445 where group_name = 'dolaccess' and 446 field_name = "dolupdate_shrink" 447 448 select @rptline = " DOL Shrink" + space(13) + 449 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 450 space(2) + 451 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 452 space(2) + 453 str(@tmp_int, 10) + space(5) + 454 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 455 print @rptline 456 457 select @tmp_int = value 458 from #tempmonitors 459 where group_name = 'dolaccess' and 460 field_name = "dolupdate_expand_incfs" 461 462 select @rptline = " DOL Cheap Expand" + space(7) + 463 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 464 space(2) + 465 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 466 space(2) + 467 str(@tmp_int, 10) + space(5) + 468 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 469 print @rptline 470 471 select @tmp_int = SUM(value) 472 from #tempmonitors 473 where group_name = 'dolaccess' and 474 field_name in ("dolupdate_expand_shift", 475 "dolupdate_expand_after_gc") 476 477 select @rptline = " DOL Expensive Expand" + space(3) + 478 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 479 space(2) + 480 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 481 space(2) + 482 str(@tmp_int, 10) + space(5) + 483 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 484 print @rptline 485 486 select @tmp_int = SUM(value) 487 from #tempmonitors 488 where group_name = 'dolaccess' and 489 field_name in ("dolupdate_forward_firstlvl", 490 "dolupdate_forward_secondlvl") 491 492 select @rptline = " DOL Expand & Forward" + space(3) + 493 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 494 space(2) + 495 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 496 space(2) + 497 str(@tmp_int, 10) + space(5) + 498 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 499 print @rptline 500 501 select @tmp_int = value 502 from #tempmonitors 503 where group_name = 'dolaccess' and 504 field_name = "dolupdate_migrate_rowhome" 505 506 select @rptline = " DOL Fwd Row Returned" + space(3) + 507 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 508 space(2) + 509 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 510 space(2) + 511 str(@tmp_int, 10) + space(5) + 512 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 513 print @rptline 514 515 print " Minimally Logged" 516 select @tmp_int = value 517 from #tempmonitors 518 where group_name = 'dolaccess' and 519 field_name = "mldml_dolupdate_replace" 520 521 select @rptline = " DOL Replace" + space(12) + 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) + @psign 528 print @rptline 529 530 select @tmp_int = value 531 from #tempmonitors 532 where group_name = 'dolaccess' and 533 field_name = "mldml_dolupdate_shrink" 534 535 select @rptline = " DOL Shrink" + space(13) + 536 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 537 space(2) + 538 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 539 space(2) + 540 str(@tmp_int, 10) + space(5) + 541 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 542 print @rptline 543 544 select @tmp_int = value 545 from #tempmonitors 546 where group_name = 'dolaccess' and 547 field_name = "mldml_dolupdate_expand_incfs" 548 549 select @rptline = " DOL Cheap Expand" + space(7) + 550 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 551 space(2) + 552 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 553 space(2) + 554 str(@tmp_int, 10) + space(5) + 555 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 556 print @rptline 557 558 select @tmp_int = SUM(value) 559 from #tempmonitors 560 where group_name = 'dolaccess' and 561 field_name in ("dmldml_olupdate_expand_shift", 562 "mldml_dolupdate_expand_after_gc") 563 564 select @rptline = " DOL Expensive Expand" + space(3) + 565 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 566 space(2) + 567 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 568 space(2) + 569 str(@tmp_int, 10) + space(5) + 570 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 571 print @rptline 572 573 select @tmp_int = SUM(value) 574 from #tempmonitors 575 where group_name = 'dolaccess' and 576 field_name in ("mldml_dolupdate_forward_firstlvl", 577 "mldml_dolupdate_forward_secondlvl") 578 579 select @rptline = " DOL Expand & Forward" + space(3) + 580 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 581 space(2) + 582 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 583 space(2) + 584 str(@tmp_int, 10) + space(5) + 585 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 586 print @rptline 587 588 select @tmp_int = value 589 from #tempmonitors 590 where group_name = 'dolaccess' and 591 field_name = "mldml_dolupdate_migrate_rowhome" 592 593 select @rptline = " DOL Fwd Row Returned" + space(3) + 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 end 602 603 print @sum1line 604 select @rptline = " Total DOL Rows Updated" + space(5) + 605 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 606 space(2) + 607 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 608 space(2) + 609 str(@tmp_total, 10) + space(5) + 610 str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign 611 print @rptline 612 print @blankline 613 614 print " Deletes" 615 616 select @tmp_total = SUM(value) 617 from #tempmonitors 618 where (group_name = 'access' and 619 field_name IN ("delete", "mldml_delete")) 620 or (group_name = 'dolaccess' and 621 field_name IN ("doldelete_total", 622 "mldml_doldelete_total")) 623 624 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 625 begin 626 select @rptline = " Total Rows Deleted 0.0 0.0 0 n/a" 627 print @rptline 628 end 629 else 630 begin 631 632 print " Fully Logged" 633 select @tmp_int = value 634 from #tempmonitors 635 where group_name = 'access' and 636 field_name = "delete_deferred" 637 638 select @rptline = " APL Deferred" + space(11) + 639 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 640 space(2) + 641 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 642 space(2) + 643 str(@tmp_int, 10) + space(5) + 644 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 645 print @rptline 646 647 /* Calc Direct Deletes as access.delete - access.delete_deferred */ 648 649 select @tmp_int2 = value - @tmp_int 650 from #tempmonitors 651 where group_name = 'access' and 652 field_name = "delete" 653 654 select @rptline = " APL Direct" + space(13) + 655 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) + 656 space(2) + 657 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) + 658 space(2) + 659 str(@tmp_int2, 10) + space(5) + 660 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign 661 print @rptline 662 663 select @tmp_int = value 664 from #tempmonitors 665 where group_name = 'dolaccess' and 666 field_name = "doldelete_total" 667 668 select @rptline = " DOL" + space(20) + 669 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 670 space(2) + 671 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 672 space(2) + 673 str(@tmp_int, 10) + space(5) + 674 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 675 print @rptline 676 677 print " Minimally Logged" 678 679 select @tmp_int = value 680 from #tempmonitors 681 where group_name = 'access' and 682 field_name = "mldml_delete" 683 684 select @rptline = " APL Direct" + space(13) + 685 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 686 space(2) + 687 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 688 space(2) + 689 str(@tmp_int, 10) + space(5) + 690 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 691 print @rptline 692 693 select @tmp_int = value 694 from #tempmonitors 695 where group_name = 'dolaccess' and 696 field_name = "mldml_doldelete_total" 697 698 select @rptline = " DOL" + space(20) + 699 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 700 space(2) + 701 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 702 space(2) + 703 str(@tmp_int, 10) + space(5) + 704 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 705 print @rptline 706 707 print @sum1line 708 select @rptline = " Total Rows Deleted" + space(9) + 709 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 710 space(2) + 711 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 712 space(2) + 713 str(@tmp_total, 10) + space(5) + 714 str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign 715 print @rptline 716 end 717 718 print @blankline 719 print @sum3line 720 select @rptline = " Total Rows Affected " + space(7) + 721 str(@NumXactOps / (@NumElapsedMs / 1000.0), 12, 1) + 722 space(2) + 723 str(@NumXactOps / convert(real, @NumXacts), 12, 1) + 724 space(2) + 725 str(@NumXactOps, 10) 726 print @rptline 727 print @sum3line 728 end /* else @NumXactOps != 0 */ 729 730 print @blankline 731 return 0 732
exec sp_procxmode 'sp_sysmon_xactsum', 'AnyMode' go Grant Execute on sp_sysmon_xactsum 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 |