Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_dcache_dtl | 31 Aug 14 | Defects Dependencies |
1 2 3 create procedure sp_sysmon_dcache_dtl 4 @NumEngines tinyint, /* number of engines online */ 5 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 6 @NumXacts int, /* for per transactions calculations */ 7 @Reco char(1) /* Flag for recommendations */ 8 as 9 /* --------- declare local variables --------- */ 10 declare @CacheName varchar(255) /* Cache Name from cache id lookup */ 11 declare @CacheID smallint /* Cache ID to map to buffer_N group */ 12 declare @NumCaches smallint /* Number of Caches to Report On */ 13 declare @TotalSearches int /* Total Cache Searches on All Caches */ 14 declare @j smallint /* loop index to iterate through multi-counter 15 ** counters (pool...) */ 16 declare @lrgpool tinyint /* boolean (0=No, 1=yes) logic to print 17 ** "Lrg Pool Not Used" Msg 18 */ 19 declare @gtlogpgszpool tinyint /* Boolean set while looking for a pool 20 ** > logical pagesize in current cache 21 ** 0 : Did not find pool 22 ** 1 : Did find a pool of current size. 23 */ 24 declare @tmp_grp varchar(25) /* temp var for build group_name's 25 ** ie. engine_N, disk_N */ 26 declare @cfg_repl varchar(24) /* configured value of replacement policy */ 27 declare @run_repl varchar(24) /* run value of replacement policy */ 28 declare @tmp_cntr varchar(35) /* temp var for build field_name's 29 ** ie. bufgrab_Nk */ 30 /* temp var for building fieldname for 31 ** searches found in buffer pool */ 32 declare @srchfound_cntr varchar(35) 33 declare @tmp_int int /* temp var for integer storage */ 34 declare @tmp_int2 int /* temp var for integer storage */ 35 declare @tmp_int3 int /* temp var for integer storage used to read 36 ** value of counter 'prefetch_kept_bp' */ 37 declare @tmp_int4 int /* temp var for integer storage used to read 38 ** value of counter 'prefetch_cached_bp' */ 39 declare @tmp_int_sum int /* temp var for integer storage 40 ** @tmp_int_sum = @tmp_int3 + @tmp_int4 */ 41 declare @tmp_total int /* temp var for summing 'total #s' data */ 42 declare @tmp_total_send int /* temp var for summing 'total #s' data */ 43 declare @tmp_total_recv int /* temp var for summing 'total #s' data */ 44 declare @tmp_float float /* temp var for float storage */ 45 declare @tmp_float2 float /* temp var for float storage */ 46 declare @numKBperpg int /* number of kilobytes per logical page */ 47 declare @subsection char(80) /* string to delimit subsections on printout */ 48 declare @sum1line char(80) /* string to delimit total lines without 49 ** percent calc on printout */ 50 declare @sum2line char(67) /* string to delimit total lines with 51 ** percent calc on printout */ 52 declare @blankline char(1) /* to print blank line */ 53 declare @psign char(3) /* hold a percent sign (%) for print out */ 54 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 55 declare @zero_str char(80) /* hold an output string for zero " 0.0" for 56 ** printing zero "% of total" */ 57 declare @rptline varchar(530) /* formatted stats line for print statement */ 58 declare @section char(80) /* string to delimit sections on printout */ 59 declare @totalrequested int /* total no of physical locks requested */ 60 declare @totalretained int /* total no of physical locks retained */ 61 declare @totaldiskreads int /* total no of diskreads completed for physical 62 ** lock acquisition.*/ 63 declare @totaldeadlocks int /* total no of deadlocks occured while taking 64 ** physical lock. */ 65 declare @totaltransfers int /* total no. of buffers transferred to this 66 ** instance. */ 67 declare @totalwaits int /* totan no. of times tasks had to wait for a 68 ** transfer to this instance. */ 69 declare @totaldiskwrites int /* total no of disk writes when a transfer 70 ** is requested.*/ 71 declare @totallockmgrcalls int /* total no. of times lock manager was 72 ** consulted for physical lock. */ 73 declare @NumElapsedSec real /* No. of elapsed seconds */ 74 75 /* ------------- Variables for Tuning Recommendations ------------*/ 76 declare @recotxt varchar(300) 77 declare @recoline char(80) 78 declare @reco_hdr_prn bit 79 declare @spinlock_contention float 80 81 /* --------- Setup Environment --------- */ 82 set nocount on /* disable row counts being sent to client */ 83 84 select @subsection = "-------------------------------------------------------------------------------" 85 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 86 select @sum2line = " ------------------------- ------------ ------------ ----------" 87 select @blankline = " " 88 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 89 select @na_str = "n/a" 90 select @zero_str = " 0.0 0.0 0 n/a" 91 select @section = "===============================================================================" 92 93 /* 94 ** Declare cursor to walk temp cache table in cache name 95 ** order to print cache-specific statistics 96 */ 97 select @TotalSearches = SUM(value) 98 from #tempmonitors 99 where group_name like "buffer_%" and 100 field_name = "bufsearch_calls" 101 102 declare cache_info cursor for 103 select cid, name, group_name, 104 ltrim(rtrim(config_replacement)), ltrim(rtrim(run_replacement)) 105 from #cachemap 106 order by name 107 for read only 108 109 open cache_info 110 fetch cache_info into @CacheID, @CacheName, @tmp_grp, @cfg_repl, @run_repl 111 112 /* 113 ** Get all Spinlock related counters and cache names 114 ** for printing potential spinlock contention. 115 */ 116 select P.field_name as name, 117 P.value as grabs, 118 W.value as waits, 119 S.value as spins into #foo 120 from #tempmonitors P, #tempmonitors W, #tempmonitors S 121 where 122 P.group_name = "spinlock_p" 123 and W.group_name = "spinlock_w" 124 and S.group_name = "spinlock_s" 125 and P.field_id = W.field_id 126 and P.field_id = S.field_id 127 and P.field_name in (select name from #cachemap) 128 129 /* Create a #temp table to store information on currently configured 130 ** pools for each cache. 131 */ 132 133 /* Initilize some variables to avoid divide by zero error */ 134 if @NumElapsedMs = 0 135 begin 136 select @NumElapsedMs = 1 137 end 138 139 if @NumXacts = 0 140 begin 141 select @NumXacts = 1 142 end 143 144 select @NumElapsedSec = @NumElapsedMs / 1000.0 145 146 while (@@sqlstatus = 0) /* { */ 147 begin 148 print @subsection 149 select @rptline = space(2) + "Cache: " + @CacheName 150 print @rptline 151 print " per sec per xact count %% of total" 152 print @sum1line 153 154 /* Print spinlock contention */ 155 156 select @spinlock_contention = 157 isnull(100.0 * (convert(float, sum(waits) / sum( 158 (case when grabs >= 0 then grabs 159 else (power(2.0, 32) + grabs) end)))), 0) 160 from #foo 161 where name = @CacheName 162 and grabs != 0 163 164 select @rptline = " Spinlock Contention" + space(15) + 165 @na_str + space(11) + 166 @na_str + space(9) + 167 @na_str + space(5) + 168 str(@spinlock_contention, 5, 1) 169 + @psign 170 print @rptline 171 print @blankline 172 173 if @TotalSearches != 0 174 begin 175 select @tmp_int = value 176 from #tempmonitors 177 where group_name = @tmp_grp and 178 field_name = "bufsearch_calls" 179 180 select @rptline = " Utilization " + space(22) + 181 @na_str + space(11) + 182 @na_str + space(9) + 183 @na_str + space(5) + 184 str(100.0 * @tmp_int / 185 @TotalSearches, 5, 1) + 186 @psign 187 188 print @rptline 189 print @blankline 190 end 191 192 print " Cache Searches" 193 194 select @tmp_total = value 195 from #tempmonitors 196 where group_name = @tmp_grp and 197 field_name = "bufsearch_calls" 198 199 if @tmp_total = 0 /* Avoid Divide by Zero Errors */ 200 begin 201 select @rptline = " Total Cache Searches 0.0 0.0 0 n/a" 202 print @rptline 203 end 204 else 205 begin 206 207 select @tmp_int = value 208 from #tempmonitors 209 where group_name = @tmp_grp and 210 field_name = "bufsearch_finds" 211 212 select @rptline = " Cache Hits" + space(13) + 213 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 214 space(2) + 215 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 216 space(2) + 217 str(@tmp_int, 10) + space(5) + 218 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 219 @psign 220 print @rptline 221 222 /* save hits for wash % and missed calc */ 223 select @tmp_int2 = @tmp_int 224 225 select @tmp_int = value 226 from #tempmonitors 227 where group_name = @tmp_grp and 228 field_name = "bufsearch_finds_in_wash" 229 230 if @tmp_int2 != 0 231 begin 232 select @rptline = " Found in Wash" + space(7) + 233 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 234 space(2) + 235 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 236 space(2) + 237 str(@tmp_int, 10) + space(5) + 238 str(100.0 * @tmp_int / @tmp_int2, 5, 1) + 239 @psign 240 print @rptline 241 end 242 243 select @tmp_int = @tmp_total - @tmp_int2 /* missed searches */ 244 245 select @rptline = " Cache Misses" + space(11) + 246 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 247 space(2) + 248 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 249 space(2) + 250 str(@tmp_int, 10) + space(5) + 251 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 252 @psign 253 print @rptline 254 end /* else @tmp_total = 0 */ 255 256 print @sum2line 257 select @rptline = " Total Cache Searches" + space(5) + 258 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 259 space(2) + 260 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 261 space(2) + 262 str(@tmp_total, 10) 263 print @rptline 264 265 print @blankline 266 267 268 select @tmp_total = SUM(value) 269 from #tempmonitors 270 where group_name = @tmp_grp and 271 field_name like "bufgrab_%k" and field_name not like "bufgrab_ref%k" 272 273 select @numKBperpg = @@maxpagesize / 1024 274 if @tmp_total = 0 275 begin 276 select @rptline = " Pool Turnover 0.0 0.0 0 n/a" 277 print @rptline 278 end 279 else 280 begin /* { */ 281 print " Pool Turnover" 282 /* init loop ctr to loop through all pool sizes */ 283 select @j = @numKBperpg 284 while (@j <= 8 * @numKBperpg) /* { */ 285 begin 286 287 /* 288 ** build pool specific counter name, 289 ** bufgrab_Nk (ie bufgrab_16k) 290 */ 291 select @tmp_cntr = "bufgrab_" + rtrim(convert(varchar(3), @j)) + "k" 292 293 select @tmp_int = value 294 from #tempmonitors 295 where group_name = @tmp_grp and 296 field_name = @tmp_cntr 297 298 if @tmp_int != 0 /* { */ 299 begin 300 301 select @rptline = space(6) + 302 convert(char(3), @j) + "Kb Pool" 303 print @rptline 304 305 select @rptline = " LRU Buffer Grab" + 306 space(4) + 307 str(@tmp_int / (@NumElapsedMs / 308 1000.0), 12, 1) + 309 space(2) + 310 str(@tmp_int / 311 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 select @tmp_cntr = "bufgrab_locked_" + 319 convert(varchar(3), @j) + "k" 320 321 select @tmp_int2 = value 322 from #tempmonitors 323 where group_name = @tmp_grp and 324 field_name = @tmp_cntr 325 326 select @rptline = space(12) + "Grabbed Locked Buffer" + 327 space(1) + 328 str(@tmp_int2 / (@NumElapsedMs / 329 1000.0), 7, 1) + 330 space(2) + 331 str(@tmp_int2 / 332 convert(real, @NumXacts), 12, 1) + 333 space(2) + 334 str(@tmp_int2, 10) + space(5) + 335 str(100.0 * @tmp_int2 / @tmp_int, 5, 1) + 336 @psign 337 print @rptline 338 339 select @tmp_cntr = "bufgrab_dirty_" + 340 convert(varchar(3), @j) + "k" 341 342 select @tmp_int2 = value 343 from #tempmonitors 344 where group_name = @tmp_grp and 345 field_name = @tmp_cntr 346 347 select @rptline = space(12) + "Grabbed Dirty" + 348 space(4) + 349 str(@tmp_int2 / (@NumElapsedMs / 350 1000.0), 12, 1) + 351 space(2) + 352 str(@tmp_int2 / 353 convert(real, @NumXacts), 12, 1) + 354 space(2) + 355 str(@tmp_int2, 10) + space(5) + 356 str(100.0 * @tmp_int2 / @tmp_int, 5, 1) + 357 @psign 358 print @rptline 359 360 end /* } if @tmp_int != 0 */ 361 362 /* get next pool size (power of 2) */ 363 select @j = @j * 2 364 365 end /* } while */ 366 367 368 print @sum2line /* calc cache turnover percent of all caches */ 369 select @rptline = " Total Cache Turnover" + space(5) + 370 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 371 space(2) + 372 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 373 space(2) + 374 str(@tmp_total, 10) 375 print @rptline 376 end /* } else @tmp_total != 0 */ 377 print @blankline 378 379 print " Cluster Cache Behavior" 380 381 select @totalrequested = sum(value) 382 from #tempmonitors 383 where group_name = @tmp_grp and 384 field_name like "physical_lock_acquisition" 385 386 select @totalretained = sum(value) 387 from #tempmonitors 388 where group_name = @tmp_grp and 389 field_name = "physical_lock_retented" 390 391 select @totaldeadlocks = sum(value) 392 from #tempmonitors 393 where group_name = @tmp_grp and 394 field_name = "physical_lock_deadlock" 395 396 select @totalwaits = sum(value) 397 from #tempmonitors 398 where group_name = @tmp_grp and 399 field_name = "waited_on_tx" 400 401 select @totaltransfers = sum(value) 402 from #tempmonitors 403 where group_name = @tmp_grp and 404 field_name = "physical_lock_txrecv" 405 406 select @totaldiskreads = sum(value) 407 from #tempmonitors 408 where group_name = @tmp_grp and 409 field_name = "diskread_lockmgr" 410 411 select @totaldiskwrites = sum(value) 412 from #tempmonitors 413 where group_name = @tmp_grp and 414 field_name = "diskwrite" 415 416 select @totallockmgrcalls = sum(value) 417 from #tempmonitors 418 where group_name = @tmp_grp and 419 field_name = "physical_lock_lockmgr" 420 421 if @totalrequested = 0 422 begin 423 print " No physical locks are acquired on buffers in this cache" 424 end 425 else 426 begin 427 select @rptline = " Total Lock Requests " + 428 str(@totalrequested / @NumElapsedSec, 12, 1) + 429 space(2) + 430 str(@totalrequested / convert(real, @NumXacts), 12, 1) + 431 space(2) + 432 str(@totalrequested, 10) + space(5) + 433 str(100.0 * @totalrequested / @totalrequested, 5, 1) + 434 @psign 435 print @rptline 436 437 select @rptline = " Retained Locks " + 438 str(@totalretained / @NumElapsedSec, 12, 1) + 439 space(2) + 440 str(@totalretained / convert(real, @NumXacts), 12, 1) + 441 space(2) + 442 str(@totalretained, 10) + space(5) + 443 str(100.0 * @totalretained / @totalrequested, 5, 1) + 444 @psign 445 print @rptline 446 447 select @rptline = " Non-retained Locks " + 448 str((@totalrequested - @totalretained) / 449 @NumElapsedSec, 12, 1) + 450 space(2) + 451 str((@totalrequested - @totalretained) / 452 convert(real, @NumXacts), 12, 1) + 453 space(2) + 454 str((@totalrequested - @totalretained), 10) + space(5) + 455 str(100.0 * (@totalrequested - @totalretained) / 456 @totalrequested, 5, 1) + 457 @psign 458 print @rptline 459 460 select @rptline = " Data Read from Disk " + 461 str(@totaldiskreads / @NumElapsedSec, 12, 1) + 462 space(2) + 463 str(@totaldiskreads / convert(real, @NumXacts), 12, 1) + 464 space(2) + 465 str(@totaldiskreads, 10) + space(5) + 466 str(100.0 * @totaldiskreads / @totalrequested, 5, 1) + 467 @psign 468 print @rptline 469 470 select @tmp_total_recv = sum(value) 471 from #tempmonitors 472 where group_name like "buffer_%" and 473 field_name = "physical_lock_txrecv" 474 475 if @tmp_total_recv > 0 476 begin 477 select @rptline = " Transfers Received " + 478 str(@totaltransfers / @NumElapsedSec, 12, 1) + 479 space(2) + 480 str(@totaltransfers / 481 convert(real, @NumXacts), 12, 1) + 482 space(2) + 483 str(@totaltransfers, 10) + space(5) + 484 str(100.0 * @totaltransfers / 485 @totalrequested, 5, 1) + 486 @psign 487 print @rptline 488 end 489 else 490 begin 491 select @rptline = " Transfers Received " + 492 str(0.0, 12, 1) + 493 space(2) + 494 str(0.0, 12, 1) + 495 space(2) + 496 str(0.0, 10) + space(5) + 497 str(0.0, 5, 1) + @psign 498 print @rptline 499 end 500 501 select @rptline = " Waited for Data Transfer" + 502 str(@totalwaits / @NumElapsedSec, 11, 1) + 503 space(2) + 504 str(@totalwaits / convert(real, @NumXacts), 12, 1) + 505 space(2) + 506 str(@totalwaits, 10) + space(5) + 507 str(100.0 * @totalwaits / @totalrequested, 5, 1) + 508 @psign 509 print @rptline 510 511 select @rptline = " Deadlocks " + 512 str(@totaldeadlocks / @NumElapsedSec, 12, 1) + 513 space(2) + 514 str(@totaldeadlocks / convert(real, @NumXacts), 12, 1) + 515 space(2) + 516 str(@totaldeadlocks, 10) + space(5) + 517 str(100.0 * @totaldeadlocks / @totalrequested, 5, 1) + 518 @psign 519 print @rptline 520 521 select @rptline = " Data Write to Disk " + 522 str(@totaldiskwrites / @NumElapsedSec, 12, 1) + 523 space(2) + 524 str(@totaldiskwrites / convert(real, @NumXacts), 12, 1) + 525 space(2) + 526 str(@totaldiskwrites, 10) + space(5) + " n/a " 527 print @rptline 528 529 select @tmp_total_send = sum(value) 530 from #tempmonitors 531 where group_name like "buffer_%" and 532 field_name = "physical_lock_txsend" 533 534 if @tmp_total_send > 0 535 begin 536 select @tmp_int = sum(value) 537 from #tempmonitors 538 where group_name = @tmp_grp and 539 field_name = "physical_lock_txsend" 540 541 select @rptline = " Transfers Sent " + 542 str(@tmp_int / @NumElapsedSec, 12, 1) + 543 space(2) + 544 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 545 space(2) + 546 str(@tmp_int, 10) + space(5) + " n/a " 547 print @rptline 548 end 549 else 550 begin 551 select @rptline = " Transfers Sent " + 552 str(0.0, 12, 1) + 553 space(2) + 554 str(0.0, 12, 1) + 555 space(2) + 556 str(0.0, 10) + space(5) + " n/a " 557 print @rptline 558 end 559 560 if (@totallockmgrcalls) = 0 561 begin 562 select @rptline = " Data Location Efficiency " + 563 space(7) + 564 @na_str + space(11) + 565 @na_str + space(9) + 566 @na_str + space(5) + 567 str(100.0, 5, 1) + 568 @psign 569 end 570 else 571 begin 572 select @rptline = " Data Location Efficiency " + 573 space(7) + 574 @na_str + space(11) + 575 @na_str + space(9) + 576 @na_str + space(5) + 577 str(100.0 - (100.0 * @totaltransfers / 578 (@totallockmgrcalls)), 5, 1) + 579 @psign 580 end 581 print @rptline 582 end 583 584 print @blankline 585 586 print " Buffer Wash Behavior" 587 588 select @tmp_total = value 589 from #tempmonitors 590 where group_name = @tmp_grp and 591 field_name = "bufwash_throughput" 592 593 if @tmp_total != 0 /* any buffers move through wash yet? */ 594 begin 595 596 select @tmp_int = value 597 from #tempmonitors 598 where group_name = @tmp_grp and 599 field_name = "bufwash_pass_clean" 600 601 select @rptline = " Buffers Passed Clean" + space(3) + 602 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 603 space(2) + 604 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 605 space(2) + 606 str(@tmp_int, 10) + space(5) + 607 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 608 @psign 609 print @rptline 610 611 select @tmp_int = value 612 from #tempmonitors 613 where group_name = @tmp_grp and 614 field_name = "bufwash_pass_writing" 615 616 select @rptline = " Buffers Already in I/O " + 617 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 618 space(2) + 619 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 620 space(2) + 621 str(@tmp_int, 10) + space(5) + 622 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 623 @psign 624 print @rptline 625 626 select @tmp_int = value 627 from #tempmonitors 628 where group_name = @tmp_grp and 629 field_name = "bufwash_write_dirty" 630 631 select @rptline = " Buffers Washed Dirty " + 632 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 633 space(2) + 634 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 635 space(2) + 636 str(@tmp_int, 10) + space(5) + 637 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 638 @psign 639 print @rptline 640 end 641 else 642 print " Statistics Not Available - No Buffers Entered Wash Section Yet" 643 644 print @blankline 645 646 print " Cache Strategy" 647 648 /* 649 ** Sum all buf unkeeps to look at % of buffers following 650 ** MRU vs Discard Strategy 651 */ 652 select @tmp_total = SUM(value) 653 from #tempmonitors 654 where group_name = @tmp_grp and 655 field_name IN ("bufunkeep_lru", "bufunkeep_mru") 656 657 if @tmp_total != 0 658 begin 659 select @tmp_int = value 660 from #tempmonitors 661 where group_name = @tmp_grp and 662 field_name = "bufunkeep_lru" 663 664 select @rptline = " Cached (LRU) Buffers " + 665 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 666 space(2) + 667 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 668 space(2) + 669 str(@tmp_int, 10) + space(5) + 670 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 671 @psign 672 print @rptline 673 674 select @tmp_int = value 675 from #tempmonitors 676 where group_name = @tmp_grp and 677 field_name = "bufunkeep_mru" 678 679 select @rptline = " Discarded (MRU) Buffers" + 680 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 681 space(2) + 682 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 683 space(2) + 684 str(@tmp_int, 10) + space(5) + 685 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 686 @psign 687 print @rptline 688 end 689 else 690 print " Statistics Not Available - No Buffers Displaced Yet" 691 692 print @blankline 693 694 print " Large I/O Usage" 695 696 select @tmp_total = value 697 from #tempmonitors 698 where group_name = @tmp_grp and 699 field_name = "prefetch_req" 700 701 if @tmp_total = 0 702 begin 703 select @rptline = " Total Large I/O Requests 0.0 0.0 0 n/a" 704 print @rptline 705 end 706 else 707 begin 708 709 select @tmp_int = SUM(value) 710 from #tempmonitors 711 where group_name = @tmp_grp and field_name IN 712 ("prefetch_as_requested", "prefetch_page_realign", "prefetch_increase") 713 714 select @rptline = " Large I/Os Performed" + space(3) + 715 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 716 space(2) + 717 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 718 space(2) + 719 str(@tmp_int, 10) + space(5) + 720 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 721 @psign 722 print @rptline 723 print @blankline 724 725 select @rptline = " Large I/Os Denied due to" 726 print @rptline 727 728 select @tmp_int = value 729 from #tempmonitors 730 where group_name = @tmp_grp and 731 field_name = "prefetch_decrease" 732 733 select @tmp_int3 = value 734 from #tempmonitors 735 where group_name = @tmp_grp and 736 field_name = "prefetch_kept_bp" 737 738 select @tmp_int4 = value 739 from #tempmonitors 740 where group_name = @tmp_grp and 741 field_name = "prefetch_cached_bp" 742 743 select @tmp_int_sum = @tmp_int3 + @tmp_int4 744 select @tmp_int = @tmp_int - @tmp_int_sum 745 746 select @rptline = " Pool < Prefetch Size" + space(1) + 747 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 748 space(2) + 749 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 750 space(2) + 751 str(@tmp_int, 10) + space(5) + 752 str(100.0 * @tmp_int / @tmp_total, 5, 1) + 753 @psign 754 print @rptline 755 756 select @rptline = " Pages Requested" 757 print @rptline 758 select @rptline = " Reside in Another" 759 print @rptline 760 761 select @rptline = " Buffer Pool" + space(10) + 762 str(@tmp_int_sum / 763 (@NumElapsedMs / 764 1000.0), 12, 1) + 765 space(2) + 766 str(@tmp_int_sum / 767 convert(real, @NumXacts), 12, 1) + 768 space(2) + 769 str(@tmp_int_sum, 10) + 770 space(5) + 771 str(100.0 * 772 @tmp_int_sum / 773 @tmp_total, 5, 1) + 774 @psign 775 print @rptline 776 print @sum2line 777 select @rptline = " Total Large I/O Requests " + 778 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 779 space(2) + 780 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 781 space(2) + 782 str(@tmp_total, 10) 783 print @rptline 784 end /* else */ 785 786 print @blankline 787 788 print " Large I/O Detail" 789 790 /* 791 ** default to NO large pools found for this cache 792 */ 793 select @lrgpool = 0 794 /* 795 ** init loop counter to loop through all large I/O pool 796 */ 797 select @j = @numKBperpg * 2 798 while (@j <= 8 * @numKBperpg) /* { */ 799 begin 800 801 /* Check that the current cache has a pool configured of size @j */ 802 select @gtlogpgszpool = count(*) 803 from #pool_detail_per_cache pd 804 where pd.io_size = convert(varchar(8), @j) 805 and name = @CacheName 806 807 if (@gtlogpgszpool > 0) 808 begin /* { */ 809 810 /* Remember that we _did_ find a large I/O pool */ 811 select @lrgpool = 1 812 813 /* 814 ** build pool specific counter name, 815 ** bufgrab_Nk (ie bufgrab_16k) 816 */ 817 select @tmp_cntr = "bufgrab_" + convert(varchar(3), @j) + "k" 818 819 select @tmp_total = value 820 from #tempmonitors 821 where group_name = @tmp_grp and 822 field_name = @tmp_cntr 823 824 select @rptline = space(5) + convert(char(4), @j) + 825 "Kb Pool" 826 print @rptline 827 828 if @tmp_total = 0 829 begin 830 select @rptline = " Pages Cached" + space(18) + 831 ltrim(@zero_str) 832 print @rptline 833 834 select @rptline = " Pages Used" + space(20) + 835 ltrim(@zero_str) 836 print @rptline 837 end 838 839 else 840 begin 841 842 /* turn # of masses into # of logical pages */ 843 select @tmp_total = @tmp_total * (@j / @numKBperpg) 844 845 select @rptline = " Pages Cached" + space(9) + 846 str(@tmp_total / 847 (@NumElapsedMs / 1000.0), 12, 1) + 848 space(2) + 849 str(@tmp_total / 850 convert(real, @NumXacts), 12, 1) + 851 space(2) + 852 str(@tmp_total, 10) + space(7) + 853 @na_str 854 print @rptline 855 856 select @tmp_cntr = "bufgrab_ref_" + 857 convert(varchar(3), @j) + "K" 858 859 select @tmp_int = value 860 from #tempmonitors 861 where group_name = @tmp_grp and 862 field_name = @tmp_cntr 863 864 select @rptline = " Pages Used" + space(11) + 865 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 866 space(2) + 867 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 868 space(2) + 869 str(@tmp_int, 10) + space(5) + 870 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 871 print @rptline 872 end 873 874 end /* } if @gtlogpgszpool > 0 */ 875 876 select @j = @j * 2 /* get next pool size */ 877 end /* } @j <= 8*@numKBperpg */ 878 879 if @lrgpool = 0 /* No large pools in this cache */ 880 begin 881 print " No Large Pool(s) In This Cache" 882 end 883 884 print @blankline 885 886 print " Dirty Read Behavior" 887 888 select @tmp_total = value 889 from #tempmonitors 890 where group_name = @tmp_grp and 891 field_name = "level0_bufpredirty" 892 893 select @rptline = " Page Requests" + space(6) + 894 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 895 space(2) + 896 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 897 space(2) + 898 str(@tmp_total, 10) + space(7) + 899 @na_str 900 print @rptline 901 print @blankline 902 903 if @Reco = 'Y' 904 begin /* { */ 905 select @recotxt = " Tuning Recommendations for Data cache : " + @CacheName 906 select @recoline = " -------------------------------------" 907 select @reco_hdr_prn = 0 908 909 /* recommendations for cache replacement policy */ 910 911 select @tmp_float = convert(int, (100.0 * a.value / b.value)) 912 from #tempmonitors a, #tempmonitors b 913 where a.group_name = @tmp_grp and 914 b.group_name = @tmp_grp and 915 a.group_name = b.group_name and 916 a.field_name = "bufsearch_finds" and 917 b.field_name = "bufsearch_calls" and 918 b.value != 0 919 if (@tmp_float is not null) 920 begin /* { */ 921 select @tmp_float2 = 0 922 select @tmp_float2 = (100.0 * a.value) / b.value 923 from #tempmonitors a, #tempmonitors b 924 where a.group_name = @tmp_grp and 925 b.group_name = @tmp_grp and 926 a.group_name = b.group_name and 927 a.field_name = "bufwash_write_dirty" and 928 b.field_name = "bufwash_throughput" and 929 b.value != 0 930 931 if (@tmp_float2 is null) 932 select @tmp_float2 = 0 933 934 /* 935 ** If the Cache Hit Rate is greater than 95% and 936 ** the replacement is less than 5% and if the 937 ** existing replacement policy is "strict LRU" 938 ** then consider using "relaxed lru replacement" 939 ** policy for this cache. 940 */ 941 if ((@tmp_float >= 95.0 and @tmp_float2 <= 5.0) and 942 @NumEngines > 1) 943 begin /* { */ 944 if (@run_repl = "strict LRU") 945 begin /* { */ 946 if (@reco_hdr_prn = 0) 947 begin /* { */ 948 print @recotxt 949 print @recoline 950 select @reco_hdr_prn = 1 951 end /* } */ 952 print " - Consider using 'relaxed LRU replacement policy'" 953 print " for this cache." 954 print @blankline 955 end /* } */ 956 end /* } */ 957 else 958 begin /* { */ 959 /* 960 ** If the Cache Hit Rate is less than 95% and 961 ** the replacement is greater than 5% and if the 962 ** existing replacement policy is "relaxed LRU" 963 ** then consider using "Strict lru replacement" 964 ** policy for this cache. 965 */ 966 if (@run_repl = "relaxed LRU") 967 begin /* { */ 968 if (@reco_hdr_prn = 0) 969 begin /* { */ 970 print @recotxt 971 print @recoline 972 select @reco_hdr_prn = 1 973 end /* } */ 974 print " - Consider using 'strict LRU replacement policy'." 975 print " for this cache." 976 print @blankline 977 end /* } */ 978 end /* } */ 979 end /* } */ 980 981 /* recommendations for pool wash size */ 982 983 select @tmp_int = SUM(value) 984 from #tempmonitors 985 where group_name = @tmp_grp and 986 field_name like "bufgrab_dirty_%" 987 988 if (@tmp_int is not null) 989 begin /* { */ 990 select @j = @numKBperpg 991 while (@j <= 8 * @numKBperpg) 992 begin /* { */ 993 994 /* 995 ** build pool specific counter name, 996 ** bufgrab_Nk (ie bufgrab_16k) 997 */ 998 select @tmp_cntr = "bufgrab_dirty_" + 999 convert(varchar(3), @j) + "k" 1000 1001 select @tmp_int = value 1002 from #tempmonitors 1003 where group_name = @tmp_grp and 1004 field_name = @tmp_cntr 1005 1006 if @tmp_int != 0 1007 begin /* { */ 1008 if (@reco_hdr_prn = 0) 1009 begin /* { */ 1010 print @recotxt 1011 print @recoline 1012 select @reco_hdr_prn = 1 1013 end /* } */ 1014 /* 1015 ** If We grabbed a buffer that was 1016 ** dirty from this pool consider increasing 1017 ** the wash size for this buffer pool 1018 */ 1019 select @rptline = " - Consider increasing the 'wash size' of the " + ltrim(str(@j, 3)) + "k pool for this cache." 1020 print @rptline 1021 print @blankline 1022 1023 end /* } */ 1024 /* get next pool size (power of 2) */ 1025 select @j = @j * 2 1026 end /* } */ 1027 1028 end /* } */ 1029 1030 /* recommendations for pool addition */ 1031 if (select value from #tempmonitors 1032 where group_name = @tmp_grp and 1033 field_name like "bufopt_lrgmass_reqd") > 0 1034 begin /* { */ 1035 if (@reco_hdr_prn = 0) 1036 begin /* { */ 1037 print @recotxt 1038 print @recoline 1039 select @reco_hdr_prn = 1 1040 end /* } */ 1041 /* 1042 ** If the optimizer wanted to do large I/O but could 1043 ** not find a buffer pool configured to be able 1044 ** to do this large I/O consider having a large I/O 1045 ** pool for this cache 1046 */ 1047 print " - Consider adding a large I/O pool for this cache." 1048 print @blankline 1049 end /* } */ 1050 1051 1052 /* recommendations for pool removal */ 1053 select @j = @numKBperpg * 2 1054 select @tmp_cntr = "bufgrab_" + 1055 convert(varchar(3), @j) + "k" 1056 select @srchfound_cntr = "bufsearch_finds_" + 1057 convert(varchar(3), @j) + "k" 1058 1059 /* 1060 ** The recommendation to remove a large buffer pool will be 1061 ** printed only when the bufgrabs and bufsearch_finds are 0 1062 ** for the buffer pool. This is to avoid 1063 ** printing this message when the data is entirely cached 1064 ** in the buffer pool and hence not having any grabs. 1065 */ 1066 if ((select value from #tempmonitors 1067 where group_name = @tmp_grp and 1068 field_name like @tmp_cntr) = 0 1069 and (select value from #tempmonitors 1070 where group_name = @tmp_grp and 1071 field_name like @srchfound_cntr) = 0 1072 and exists (select * from #pool_detail_per_cache 1073 where io_size = convert(varchar(8), @j) 1074 and name = @CacheName)) 1075 begin /* { */ 1076 if (@reco_hdr_prn = 0) 1077 begin /* { */ 1078 print @recotxt 1079 print @recoline 1080 select @reco_hdr_prn = 1 1081 end /* } */ 1082 /* 1083 ** If there are no grabs for this buffer pool 1084 ** consider removing this buffer pool. 1085 */ 1086 select @rptline = " - Consider removing the " + ltrim(str(@j, 3)) + "k pool for this cache." 1087 print @rptline 1088 print @blankline 1089 end /* } */ 1090 1091 select @j = @j * 2 1092 select @tmp_cntr = "bufgrab_" + convert(varchar(3), @j) + "k" 1093 select @srchfound_cntr = "bufsearch_finds_" + 1094 convert(varchar(3), @j) + "k" 1095 1096 if ((select value from #tempmonitors 1097 where group_name = @tmp_grp and 1098 field_name like @tmp_cntr) = 0 1099 and (select value from #tempmonitors 1100 where group_name = @tmp_grp and 1101 field_name like @srchfound_cntr) = 0 1102 and exists (select * from #pool_detail_per_cache 1103 where io_size = convert(varchar(8), @j) 1104 and name = @CacheName)) 1105 begin /* { */ 1106 if (@reco_hdr_prn = 0) 1107 begin /* { */ 1108 print @recotxt 1109 print @recoline 1110 select @reco_hdr_prn = 1 1111 end /* } */ 1112 /* 1113 ** If there are no grabs for this buffer pool 1114 ** consider removing this buffer pool. 1115 */ 1116 select @rptline = " - Consider removing the " + ltrim(str(@j, 3)) + "k pool for this cache." 1117 print @rptline 1118 print @blankline 1119 end /* } */ 1120 1121 select @j = @j * 2 1122 select @tmp_cntr = "bufgrab_" + convert(varchar(3), @j) + "k" 1123 select @srchfound_cntr = "bufsearch_finds_" + 1124 convert(varchar(3), @j) + "k" 1125 1126 if ((select value from #tempmonitors 1127 where group_name = @tmp_grp and 1128 field_name like @tmp_cntr) = 0 1129 and (select value from #tempmonitors 1130 where group_name = @tmp_grp and 1131 field_name like @srchfound_cntr) = 0 1132 and exists (select * from #pool_detail_per_cache 1133 where io_size = convert(varchar(8), @j) 1134 and name = @CacheName)) 1135 begin /* { */ 1136 if (@reco_hdr_prn = 0) 1137 begin /* { */ 1138 print @recotxt 1139 print @recoline 1140 select @reco_hdr_prn = 1 1141 end /* } */ 1142 /* 1143 ** If there are no grabs for this buffer pool 1144 ** consider removing this buffer pool. 1145 */ 1146 select @rptline = " - Consider removing the " + ltrim(str(@j, 3)) + "k pool for this cache." 1147 print @rptline 1148 print @blankline 1149 end /* } */ 1150 1151 /* recommendations for cache splitting */ 1152 1153 /* 1154 ** If the number of engines is > 1 1155 ** and if the contention on the buffer 1156 ** manager spinlock is > 10% 1157 ** consider using cache partitions or named caches 1158 ** or both 1159 ** Also there are potential conditions where the waits or 1160 ** grabs might go negative because of the counter overflowing 1161 ** what an integer can hold; Cover for those cases as well. 1162 */ 1163 if (@NumEngines > 1 and (@spinlock_contention >= 10 or 1164 @spinlock_contention < 0)) 1165 begin /* { */ 1166 if (@reco_hdr_prn = 0) 1167 begin /* { */ 1168 print @recotxt 1169 print @recoline 1170 select @reco_hdr_prn = 1 1171 end /* } */ 1172 print " - Consider using Named Caches or Cache partitions or both." 1173 print @blankline 1174 end /* } */ 1175 end /* } */ 1176 1177 fetch cache_info into @CacheID, @CacheName, @tmp_grp, @cfg_repl, @run_repl 1178 end /* } while @@sqlstatus */ 1179 1180 close cache_info 1181 deallocate cursor cache_info 1182 1183 return 0 1184
exec sp_procxmode 'sp_sysmon_dcache_dtl', 'AnyMode' go Grant Execute on sp_sysmon_dcache_dtl to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table tempdb..#tempmonitors (1) reads table tempdb..#cachemap (1) reads table tempdb..#pool_detail_per_cache (1) read_writes table tempdb..#foo (1) CALLERS called by proc sybsystemprocs..sp_sysmon_dcache called by proc sybsystemprocs..sp_sysmon_analyze called by proc sybsystemprocs..sp_sysmon |