| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_dcachestats | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_sysmon_dcachestats 3 @top_n_objs varchar(14), /* top 'n' objects to report on */ 4 @cachename varchar(255), /* cache name */ 5 @seconds int, 6 @Reco_option char(1) /* print recommendations */ 7 as 8 9 declare @blankline char(80) 10 declare @separator_line char(80) 11 declare @rpt_line char(80) /* formatted line for print */ 12 declare @exec_str char(1250) /* used in exec immediate */ 13 declare @ldspc char(1) 14 declare @col_sep char(1) 15 declare @psign char(1) /* percent sign */ 16 declare @ret int 17 declare @cache_size_in_KB int 18 declare @top_n int /* int value of top_n_objs */ 19 20 declare @cache_sz_str varchar(13) 21 declare @cache_hit_per varchar(6) 22 declare @cache_usage_per varchar(6) 23 declare @cache_sz_used real 24 declare @logical_reads int 25 declare @physical_reads int 26 declare @spinlock_contention float 27 declare @cache_partitions int 28 declare @cache_id int 29 30 declare @db_name varchar(255) 31 declare @obj_dbid int 32 declare @tab_id int 33 declare @obj_ind_id int 34 declare @obj_size int 35 declare @obj_lreads int 36 declare @obj_preads int 37 declare @obj_cached_KB int 38 declare @cache_occp_by_obj_per varchar(6) 39 declare @obj_hit_per varchar(6) 40 declare @display_order int /* 1 - desc, 0 - asc */ 41 declare @begin_time datetime /* values were cleared */ 42 declare @end_time datetime /* values are reported */ 43 declare @cut_off_lreads int /* value that decides rows 44 ** selection */ 45 declare @max_objname_len int /* auto format object name */ 46 declare @tmpval int 47 declare @reco varchar(255) /* recommendation string */ 48 declare @max_reco_len int 49 declare @num_engines int 50 declare @numKBperpg int 51 declare @tmp_int int 52 declare @iter_cnt int 53 declare @tmp_cntr varchar(35) 54 declare @tmp_grp varchar(18) /* group id in sysmonitors */ 55 56 declare @LReads_col varchar(6) 57 declare @PReads_col varchar(6) 58 declare @LReads_rename_col varchar(6) 59 declare @PReads_rename_col varchar(6) 60 declare @Usage_Per_col varchar(6) 61 declare @Run_Size_col varchar(8) 62 declare @Cache_Partitions_col varchar(16) 63 declare @Spin_Contention_col varchar(20) 64 declare @Hit_Per_col varchar(5) 65 declare @IO_Size_col varchar(7) 66 declare @Wash_col varchar(9) 67 declare @APF_Per_col varchar(5) 68 declare @APF_Eff_Per_col varchar(9) 69 declare @Object_col varchar(6) 70 declare @Obj_Size_col varchar(8) 71 declare @Size_In_Cache_col varchar(13) 72 declare @Obj_Cached_Per_col varchar(12) 73 declare @Cache_Occupied_Per_col varchar(12) 74 75 declare @tempdbname varchar(30) /* assigned tempdb */ 76 77 select @blankline = " " 78 select @rpt_line = " " 79 select @separator_line = replicate("-", 80) 80 select @ldspc = " " 81 select @col_sep = ":" 82 select @psign = "%" 83 select @numKBperpg = @@maxpagesize / 1024 84 85 select @LReads_col = "LReads" 86 select @PReads_col = "PReads" 87 select @LReads_rename_col = "LR/sec" 88 select @PReads_rename_col = "PR/sec" 89 select @Usage_Per_col = "Usage%" 90 select @Run_Size_col = "Run Size" 91 select @Cache_Partitions_col = "Cache Partitions" 92 select @Spin_Contention_col = "Spinlock Contention%" 93 select @Hit_Per_col = "Hit%" 94 select @IO_Size_col = "IO Size" 95 select @Wash_col = "Wash Size" 96 select @APF_Per_col = "APF%" 97 select @APF_Eff_Per_col = "APF-Eff%" 98 select @Object_col = "Object" 99 select @Obj_Size_col = "Obj Size" 100 select @Size_In_Cache_col = "Size in Cache" 101 select @Obj_Cached_Per_col = "Obj_Cached%" 102 select @Cache_Occupied_Per_col = "Cache_Occp%" 103 104 create table #obj_details(dbid int, owner varchar(30), objid int, 105 indid int, index_name varchar(30), 106 size_KB int) 107 108 create table #recommendations_tab(reco_col varchar(255)) 109 110 select co.config, parent, convert(char(30), co.name) name, 111 convert(char(30), co.comment) comment, cu.value run_size, 112 memory_used wash_size, apf_percent apf_value 113 into #syscacheconfig 114 from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu 115 where parent = 19 116 and co.config = cu.config 117 and co.name = cu.comment 118 order by name, config 119 120 select @begin_time = min(InsertTime), 121 @end_time = max(InsertTime) 122 from tempdb.dbo.tempcachestats 123 124 select @top_n = convert(int, @top_n_objs) 125 select @display_order = sign(@top_n) 126 if @display_order = 0 127 begin 128 select @display_order = 1 129 end 130 select @top_n = abs(@top_n) 131 132 set nocount on 133 134 /* Create the diff tables */ 135 select 1 as "PrintOrder", cs2.CacheName, cs2.CachePartitions, cs2.CacheID, 136 cs2.LogicalReads - cs1.LogicalReads as LogicalReads, 137 cs2.PhysicalReads - cs1.PhysicalReads as PhysicalReads 138 into #tempcachestats 139 from tempdb.dbo.tempcachestats cs1, 140 tempdb.dbo.tempcachestats cs2 141 where cs2.CacheName = cs1.CacheName 142 and cs2.InsertTime = @end_time 143 and cs1.InsertTime = @begin_time 144 145 update #tempcachestats 146 set PrintOrder = 0 147 where CacheName = 'default data cache' 148 149 select bs2.CacheName, 150 bs2.IOBufferSize, 151 bs2.AllocatedKB, 152 bs2.PagesTouched * (@@maxpagesize / 1024) as PoolUsed, 153 bs2.PhysicalReads - bs1.PhysicalReads as PhysicalReads, 154 tm1.value + tm2.value as LReads, 155 tm2.value as PReads, 156 tm3.value as APFs_Used 157 into #tempbufpoolstats 158 from tempdb.dbo.tempbufpoolstats bs2, 159 tempdb.dbo.tempbufpoolstats bs1, 160 #tempmonitors tm1, 161 #tempmonitors tm2, 162 #tempmonitors tm3 163 where bs2.InsertTime = @end_time 164 and bs1.InsertTime = @begin_time 165 and bs2.CacheName = bs1.CacheName 166 and bs2.IOBufferSize = bs1.IOBufferSize 167 and tm1.group_name = "buffer_" 168 + convert(varchar(4), bs2.CacheID) 169 and tm2.group_name = tm1.group_name 170 and tm3.group_name = tm1.group_name 171 and tm1.field_name = "bufsearch_finds_" 172 + convert(varchar(3), bs2.IOBufferSize / 1024) + "k" 173 and tm2.field_name = "bufread_" 174 + convert(varchar(3), bs2.IOBufferSize / 1024) + "k" 175 and tm3.field_name = "apf_ios_used_" 176 + convert(varchar(3), bs2.IOBufferSize / 1024) + "k" 177 178 179 select InsertTime, sum(CachedKB) as CachedKB, CacheName, 180 DBID, ObjectID, IndexID 181 into #tempcachedobjstats 182 from tempdb.dbo.tempcachedobjstats 183 group by InsertTime, ObjectID, IndexID 184 185 select co1.CacheName, 186 os2.LogicalReads - os1.LogicalReads as LogicalReads, 187 os2.PhysicalReads - os1.PhysicalReads as PhysicalReads, 188 os2.DBID, os2.ObjectID, os2.IndexID, 189 0 as CachedKB 190 into #tempobjstats 191 from tempdb.dbo.tempobjstats os1, 192 tempdb.dbo.tempobjstats os2, 193 #tempcachedobjstats co1 194 where os1.DBID *= co1.DBID 195 and os1.ObjectID *= co1.ObjectID 196 and os1.IndexID *= co1.IndexID 197 and os2.DBID = os1.DBID 198 and os2.ObjectID = os1.ObjectID 199 and os2.IndexID = os1.IndexID 200 and os2.InsertTime = @end_time 201 and os1.InsertTime = @begin_time 202 and co1.InsertTime = @end_time 203 union 204 select co.CacheName, 205 os.LogicalReads, 206 os.PhysicalReads, 207 os.DBID, os.ObjectID, os.IndexID, 208 co.CachedKB 209 from tempdb.dbo.tempobjstats os, 210 #tempcachedobjstats co 211 where os.DBID = co.DBID 212 and os.ObjectID = co.ObjectID 213 and os.IndexID = co.IndexID 214 and os.InsertTime = @end_time 215 and co.InsertTime = @end_time 216 and not exists 217 (select ObjectID from tempdb.dbo.tempobjstats 218 where ObjectID = os.ObjectID 219 and ObjectID = co.ObjectID 220 and DBID = os.DBID 221 and IndexID = os.IndexID 222 and InsertTime = @begin_time) 223 224 /* Objects that have 0 as LogicalReads are removed */ 225 delete #tempobjstats 226 where LogicalReads = 0 227 228 update #tempobjstats 229 set os.CachedKB = co.CachedKB, 230 os.CacheName = co.CacheName 231 from #tempobjstats os, 232 #tempcachedobjstats co 233 where os.DBID = co.DBID 234 and os.ObjectID = co.ObjectID 235 and os.IndexID = co.IndexID 236 and co.InsertTime = @end_time 237 238 /* 239 ** create cursor to process cache rows 240 */ 241 if (@cachename = "NULL") 242 begin 243 select @cachename = "%" 244 end 245 246 declare named_cache_cursor cursor for 247 select CacheName, 248 CachePartitions, 249 CacheID, 250 LogicalReads, 251 PhysicalReads 252 from #tempcachestats 253 where CacheName like @cachename 254 order by PrintOrder, CacheName 255 for read only 256 257 /* 258 ** Declare cursor for storing sampled object stats 259 */ 260 declare objstats_cursor cursor for 261 select LogicalReads, PhysicalReads, 262 DBID, ObjectID, IndexID, CachedKB 263 from #tempobjstats 264 where CacheName like @cachename 265 and 266 ((@display_order = 1 and LogicalReads >= @cut_off_lreads) 267 or 268 (@display_order = - 1 and LogicalReads <= @cut_off_lreads) 269 ) 270 order by PhysicalReads desc, (LogicalReads * @display_order) desc 271 for read only 272 273 open named_cache_cursor 274 fetch named_cache_cursor into @cachename, @cache_partitions, @cache_id, 275 @logical_reads, @physical_reads 276 277 dump tran tempdb with truncate_only 278 279 /* 280 ** If assigned temporary database is different than system tempdb, then 281 ** dump that one as well. 282 */ 283 select @tempdbname = db_name(@@tempdbid) 284 if (@tempdbname != "tempdb") 285 begin 286 dump tran @tempdbname with truncate_only 287 end 288 289 select @num_engines = count(*) from #tempmonitors 290 where field_name = "clock_ticks" 291 and group_name like "engine_%" 292 and value > 0 293 294 /* 295 ** Print Report Header 296 */ 297 select @rpt_line = replicate("=", 80) 298 print @rpt_line 299 300 select @rpt_line = space(34) + "Cache Wizard" + space(34) 301 print @rpt_line 302 303 select @rpt_line = replicate("=", 80) 304 print @rpt_line 305 306 while (@@sqlstatus = 0) 307 begin 308 print @blankline 309 select @cachename = ltrim(rtrim(@cachename)) 310 select @rpt_line = replicate('-', datalength(@cachename)) 311 print @rpt_line 312 313 print @cachename 314 315 print @rpt_line 316 317 select @cache_size_in_KB = run_size from #syscacheconfig 318 where config = 19 319 and parent = 19 320 and name = @cachename 321 322 select @cache_sz_str = str(@cache_size_in_KB * 1.0 / 1024, 9, 2) + " Mb" 323 324 select @cache_sz_used = sum(PoolUsed), 325 @logical_reads = sum(LReads), 326 @physical_reads = sum(PReads) 327 from #tempbufpoolstats 328 where CacheName like @cachename 329 330 if (@logical_reads = 0) 331 begin 332 select @cache_hit_per = " n/a" 333 end 334 else 335 begin 336 select @cache_hit_per = str(((@logical_reads - @physical_reads) 337 * 100.0 338 / @logical_reads), 6, 2) 339 end 340 341 select @cache_usage_per = str(@cache_sz_used * 100.0 342 / @cache_size_in_KB, 6, 2) 343 344 /* P.value is grabs and W.value is waits */ 345 select @spinlock_contention = isnull(100.0 * (sum(W.value) / sum(P.value)), 0) 346 from #tempmonitors P, 347 #tempmonitors W 348 where P.field_name = @cachename 349 and P.group_name = "spinlock_p" 350 and W.group_name = "spinlock_w" 351 and P.field_id = W.field_id 352 and P.value > 0 353 354 select @rpt_line = @Run_Size_col + @ldspc + @ldspc 355 + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc 356 + @ldspc + @col_sep 357 + @cache_sz_str + @ldspc + @ldspc + @ldspc 358 + @Usage_Per_col + @psign + @ldspc + @ldspc 359 + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc 360 + @ldspc + @ldspc + @ldspc + @ldspc 361 + @ldspc + @ldspc + @ldspc 362 + @col_sep + @ldspc + @ldspc + @ldspc + @ldspc 363 + @cache_usage_per 364 print @rpt_line 365 366 /* If the sampled period is less than 1 sec then @seconds will be '0'.*/ 367 /* Round @seconds to '1' if it is '0' to avoid devide by zero problems */ 368 369 if (@seconds = 0) select @seconds = 1 370 371 select @rpt_line = @LReads_rename_col + @ldspc + @ldspc 372 + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc 373 + @ldspc + @ldspc + @ldspc 374 + @col_sep 375 + str(@logical_reads * 1.0 / @seconds, 9, 2) 376 + @ldspc + @ldspc + @ldspc + @ldspc 377 + @ldspc + @ldspc 378 + @PReads_rename_col + @ldspc + @ldspc + @ldspc 379 + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc 380 + @ldspc + @ldspc + @ldspc 381 + @ldspc + @ldspc + @ldspc 382 + @col_sep + @ldspc 383 + str(@physical_reads * 1.0 / @seconds, 9, 2) 384 + @ldspc + @ldspc 385 + @ldspc + @Hit_Per_col + @psign + @col_sep + @ldspc 386 + @cache_hit_per 387 print @rpt_line 388 389 select @rpt_line = @Cache_Partitions_col + @col_sep 390 + str(@cache_partitions, 9) + @ldspc + @ldspc 391 + @ldspc + @ldspc + @ldspc + @ldspc 392 + @Spin_Contention_col + @psign 393 + @col_sep + @ldspc + @ldspc + @ldspc + @ldspc 394 + str(@spinlock_contention, 6, 2) 395 print @rpt_line 396 397 print @blankline 398 399 /* 400 ** Print the Buffer Pool Stats 401 */ 402 select @rpt_line = "Buffer Pool Information" 403 print @rpt_line 404 405 print @separator_line 406 407 select @exec_str = "select str(IOBufferSize / 1024, 3) + ' Kb' as '" 408 + @IO_Size_col 409 + "', str(wash_size, 7) + ' Kb' as '" 410 + @Wash_col 411 + "', str(AllocatedKB * 1.0 / 1024, 8, 2) + ' Mb' as '" 412 + @Run_Size_col 413 + "', str(apf_value, 6, 2) as '" 414 + @APF_Per_col 415 + "', str(" + @LReads_col + " * 1.0 / " 416 + convert(varchar(6), @seconds) 417 + ", 8, 2) as '" + @LReads_rename_col 418 + "', str(" + @PReads_col + " * 1.0 / " 419 + convert(varchar(6), @seconds) 420 + ", 8, 2) as '" + @PReads_rename_col 421 + "', case when (" 422 + @LReads_col 423 + " = 0) then ' n/a' else str((" 424 + @LReads_col + " - " + @PReads_col 425 + ") * 100.0 / (" 426 + @LReads_col 427 + "), 6, 2) end as '" + @Hit_Per_col 428 + "', case when (PhysicalReads - " 429 + @PReads_col + " <= 0) then ' n/a' else str(" 430 + "APFs_Used * 100.0 / (PhysicalReads - " 431 + @PReads_col + "), 6, 2) end as '" 432 + @APF_Eff_Per_col 433 + "', str(PoolUsed * 100.0 / AllocatedKB, 6, 2) as '" 434 + @Usage_Per_col 435 + "' from #syscacheconfig, #tempbufpoolstats " 436 + "where config = (19 + floor(1.45 * " 437 + "log(IOBufferSize / 1024))) and parent=19 " 438 + " and name='" + @cachename 439 + "' and CacheName like '" + @cachename 440 + "' order by IOBufferSize desc" 441 exec (@exec_str) 442 print @blankline 443 444 if @Reco_option = 'Y' 445 begin 446 if (@cache_sz_used * 100.0 / @cache_size_in_KB < 5.0) 447 begin 448 select @reco = @Usage_Per_col + " for '" 449 + @cachename + "'" 450 + " is low (< 5%)" 451 insert into #recommendations_tab(reco_col) 452 values (@reco) 453 end 454 455 select @exec_str = "insert into #recommendations_tab " 456 + " select '" + @Usage_Per_col + "'" 457 + " + ' for '" 458 + " + str(IOBufferSize/1024, 2) " 459 + " + 'k buffer pool in cache:' + CacheName" 460 + " + ' is low (< 5%)' " 461 + "from #tempbufpoolstats " 462 + "where PoolUsed * 100.0 / AllocatedKB < 5.0" 463 + " and CacheName like '" + @cachename + "'" 464 + " order by IOBufferSize desc" 465 exec (@exec_str) 466 467 select @tmp_grp = "buffer_" 468 + convert(varchar(4), @cache_id) 469 470 if (@num_engines > 1 and (@spinlock_contention >= 10 or @spinlock_contention < 0)) 471 begin /* { */ 472 select @reco = "Consider using Named Caches or creating more cache partitions for '" + @cachename + "' or both." 473 insert into #recommendations_tab(reco_col) 474 values (@reco) 475 end /* } */ 476 477 /* recommendations for pool wash size */ 478 select @tmp_int = SUM(value) 479 from #tempmonitors 480 where group_name = @tmp_grp 481 and field_name like "bufgrab_dirty_%" 482 483 if (@tmp_int is not null) 484 begin /* { */ 485 select @iter_cnt = @numKBperpg 486 while (@iter_cnt <= 8 * @numKBperpg) 487 begin /* { */ 488 /* 489 ** build pool specific counter name, 490 ** bufgrab_Nk (ie bufgrab_16k) 491 */ 492 select @tmp_cntr = "bufgrab_dirty_" 493 + convert(varchar(3), @iter_cnt) + "k" 494 495 select @tmp_int = value 496 from #tempmonitors 497 where group_name = @tmp_grp and 498 field_name = @tmp_cntr 499 500 if @tmp_int != 0 501 begin /* { */ 502 /* 503 ** If We grabbed a buffer that was 504 ** dirty from this pool consider 505 ** increasing the wash size for 506 ** this buffer pool 507 */ 508 select @reco = 509 "Consider increasing the 'wash size' of the " + ltrim(str(@iter_cnt, 3)) + "k pool for '" + @cachename + "'" 510 insert into #recommendations_tab(reco_col) values (@reco) 511 512 end /* } */ 513 /* get next pool size (power of 2) */ 514 select @iter_cnt = @iter_cnt * 2 515 end /* } */ 516 517 end /* } */ 518 519 /* recommendations for pool addition */ 520 if (select value from #tempmonitors 521 where group_name = @tmp_grp 522 and field_name like "bufopt_lrgmass_reqd") > 0 523 begin /* { */ 524 select @reco = 525 "Consider adding a large I/O pool for '" 526 + @cachename + "'" 527 insert into #recommendations_tab(reco_col) 528 values (@reco) 529 end /* } */ 530 531 insert into #recommendations_tab(reco_col) values (@blankline) 532 end 533 print @blankline 534 535 /* 536 ** We want to order the objects by LogicalReads and then while printing 537 ** order them by PhysicalReads. The ordering being 'desc' or 'ascending' 538 ** is decided by @display_order. To achieve this, we will select the 539 ** cut-off 540 ** value from the ordered table, which will be the value at row position 541 ** @top_n when @top_n > 0. In case of 'asc', we will delete all values 542 ** greater than this value and in case of 'desc', delete all values 543 ** lesser than this value. 544 */ 545 set rowcount @top_n 546 select @cut_off_lreads = LogicalReads, 547 @tmpval = LogicalReads * @display_order 548 from #tempobjstats 549 where CacheName = @cachename 550 order by 2 desc 551 552 set rowcount 0 553 554 open objstats_cursor 555 fetch objstats_cursor into @obj_lreads, 556 @obj_preads, @obj_dbid, 557 @tab_id, @obj_ind_id, 558 @obj_cached_KB 559 /* 560 ** Printing object stats 561 */ 562 select @rpt_line = "Object Statistics" 563 print @rpt_line 564 565 print @separator_line 566 print @blankline 567 568 while (@@sqlstatus = 0) 569 begin 570 select @db_name = db_name(@obj_dbid) 571 572 select @exec_str = "exec " + @db_name + 573 "..sp_dcachestats_obj_details " 574 + str(@tab_id) 575 + ", " 576 + str(@obj_ind_id) 577 exec (@exec_str) 578 579 fetch objstats_cursor into @obj_lreads, 580 @obj_preads, @obj_dbid, 581 @tab_id, @obj_ind_id, 582 @obj_cached_KB 583 584 end 585 586 close objstats_cursor 587 588 select @max_objname_len = max(datalength(db_name(dbid)) 589 + datalength(owner) 590 + datalength(object_name(objid, dbid)) 591 + datalength(index_name)) 592 + 3 593 from #obj_details 594 595 /* Print only when objects are present */ 596 if @max_objname_len is not NULL 597 begin 598 select @exec_str = " select convert(varchar(" 599 + convert(varchar(3), @max_objname_len) 600 + "), db_name(o.dbid) + '.' + owner + '.' + " 601 + "isnull(object_name(o.objid, o.dbid)," 602 + "convert(varchar(10), o.objid)) + case when " 603 + " o.indid != 0 then '.' + o.index_name end) as " 604 + @Object_col 605 + ", str(LogicalReads * 1.0 / " 606 + convert(varchar(6), @seconds) 607 + ", 7, 2) as '" + @LReads_rename_col 608 + "', str(PhysicalReads * 1.0 / " 609 + convert(varchar(6), @seconds) 610 + ", 7, 2) as '" + @PReads_rename_col 611 + "', case when LogicalReads = 0 then ' n/a' else " 612 + " str(100.0 * (LogicalReads - PhysicalReads)" 613 + " / LogicalReads, 6, 2) end as '" 614 + @Hit_Per_col 615 + "', case when size_KB = 0 then ' n/a' else " 616 + "str(CachedKB * 100.0 / size_KB, 11, 2) end " 617 + "as '" + @Obj_Cached_Per_col 618 + "', str(CachedKB * 100.0 / " 619 + convert(varchar(8), @cache_size_in_KB) 620 + ", 11, 2) as '" 621 + @Cache_Occupied_Per_col 622 + "' from #tempobjstats t, #obj_details o " 623 + " where t.DBID = o.dbid and t.ObjectID = o.objid " 624 + " and t.IndexID = o.indid " 625 + " order by PhysicalReads desc, (LogicalReads * " 626 + convert(varchar(2), @display_order) 627 + ") desc" 628 629 exec (@exec_str) 630 631 print @blankline 632 633 select @exec_str = " select convert(varchar(" 634 + convert(varchar(3), @max_objname_len) 635 + "), db_name(o.dbid) + '.' + owner + '.' + " 636 + " isnull(object_name(o.objid, o.dbid)," 637 + "convert(varchar(10), objid)) + case when " 638 + " o.indid != 0 then '.' + index_name end) as " 639 + @Object_col 640 + ", str(size_KB, 8) + ' Kb' as '" 641 + @Obj_Size_col 642 + "', str(CachedKB, 8) + ' Kb' as '" 643 + @Size_In_Cache_col 644 + "' from #tempobjstats t, #obj_details o " 645 + " where t.DBID = o.dbid and t.ObjectID = o.objid " 646 + " and t.IndexID = o.indid" 647 + " order by PhysicalReads desc, (LogicalReads * " 648 + convert(varchar(2), @display_order) 649 + ") desc" 650 exec (@exec_str) 651 652 /* 653 ** truncate #obj_details so that the output is not repeated 654 ** for the previous cache' objects 655 */ 656 truncate table #obj_details 657 end 658 else 659 begin 660 print "No Activity for objects in this interval" 661 end 662 663 fetch named_cache_cursor into @cachename, @cache_partitions, @cache_id, 664 @logical_reads, @physical_reads 665 end 666 print @blankline 667 select @max_reco_len = isnull(max(datalength(reco_col)), 1) 668 from #recommendations_tab 669 select @exec_str = "if (select count(*) from #recommendations_tab " 670 + "where reco_col != '" + @blankline 671 + "' ) > 0 " + " begin select convert(varchar(" 672 + convert(varchar(3), @max_reco_len) 673 + "), reco_col) as 'TUNING RECOMMENDATIONS' " 674 + "from #recommendations_tab end" 675 exec (@exec_str) 676 677 print @blankline 678 print "LEGEND" 679 select @rpt_line = replicate('-', 6) 680 print @rpt_line 681 print "%1! - number of logical reads per second, i.e. sum of cache & disk reads", 682 @LReads_rename_col 683 print "%1! - number of physical reads per second i.e. disk reads", 684 @PReads_rename_col 685 print "%1! - size of cache or buffer pool in Kilobytes", 686 @Run_Size_col 687 print "%1! - number of cache partitions", @Cache_Partitions_col 688 print "%1! - Percentage spinlock contention for the cache", @Spin_Contention_col 689 print "%1! - ratio of hits to total searches", @Hit_Per_col 690 print "%1! - ratio of pages referenced to Run Size", @Usage_Per_col 691 print @blankline 692 print "%1! - wash size of buffer pool in Kilobytes", @Wash_col 693 print "%1! - asynchronous prefetch %% for this buffer pool", 694 @APF_Per_col 695 print "%1! - Ratio of buffers found in cache and brought in because", 696 @APF_Eff_Per_col 697 print " of APF to the number of APF disk reads performed" 698 print @blankline 699 print "%1! - combination of db, owner, object and index name", 700 @Object_col 701 print "%1! - size of the object in Kilobytes", @Obj_Size_col 702 print "%1! - size occupied in cache in Kilobytes at the end of sample", 703 @Size_In_Cache_col 704 print "%1! - Ratio of 'Size in Cache' to 'Obj Size'", @Obj_Cached_Per_col 705 print "%1! - Ratio of 'Size in Cache' to 'Run Size' of cache", 706 @Cache_Occupied_Per_col 707 close named_cache_cursor 708 deallocate cursor named_cache_cursor 709 deallocate cursor objstats_cursor 710 return 0 711
exec sp_procxmode 'sp_sysmon_dcachestats', 'AnyMode' go Grant Execute on sp_sysmon_dcachestats to public go
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#recommendations_tab (1) read_writes table tempdb..#tempbufpoolstats (1) read_writes table tempdb..#tempcachestats (1) read_writes table tempdb..#obj_details (1) reads table master..syscurconfigs (1)  read_writes table tempdb..#syscacheconfig (1) read_writes table tempdb..#tempobjstats (1) read_writes table tempdb..#tempcachedobjstats (1) reads table tempdb..#tempmonitors (1) reads table master..sysconfigures (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze_mda  called by proc sybsystemprocs..sp_sysmon  |
