sp_sysmon '00:00:30', 'cache wizard' ============================================================================= Cache Wizard ============================================================================= ------------------ default data cache ------------------ Run Size : 100.00 Mb Usage% : 2.86 LR/sec : 41.10 PR/sec : 22.57 Hit%: 45.09 Cache Partitions: 4 Spinlock Contention%: 0.00 Buffer Pool Information ----------------------------------------------------------------------------- IO Size Wash Size Run Size APF% LR/sec PR/sec Hit% APF-Eff% Usage% ------- ---------- ----------- ------ -------- -------- ------ -------- ------ 4 Kb 3276 Kb 16.00 Mb 10.00 0.47 0.13 71.43 n/a 0.20 2 Kb 17200 Kb 84.00 Mb 10.00 40.63 22.43 44.79 n/a 3.37 (1 row affected) Object Statistics ----------------------------------------------------------------------------- Object LR/sec PR/sec Hit% Obj_Cached% Cache_Occp% ------------------------------- ------- ------- ------ ----------- ----------- empdb.dbo.t1 0.57 0.30 47.06 56.25 0.02 empdb.dbo.t2 0.30 0.30 0.00 56.25 0.02 empdb.dbo.t3 0.30 0.30 0.00 56.25 0.02 empdb.dbo.t4 0.30 0.30 0.00 56.25 0.02 empdb.dbo.t5 0.30 0.30 0.00 56.25 0.02 empdb.dbo.t6 0.30 0.30 0.00 56.25 0.02 empdb.dbo.t8 0.30 0.30 0.00 56.25 0.02 empdb.dbo.t7 0.57 0.20 64.71 62.50 0.02 tempdb.dbo.tempcachedobjstats 3.63 0.00 100.00 50.00 0.01 tempdb.dbo.tempobjstats 0.47 0.00 100.00 25.00 0.00 Object Obj Size Size in Cache ------------------------------- ----------- ------------- empdb.dbo.t1 32 Kb 18 Kb empdb.dbo.t2 32 Kb 18 Kb empdb.dbo.t3 32 Kb 18 Kb empdb.dbo.t4 32 Kb 18 Kb empdb.dbo.t5 32 Kb 18 Kb empdb.dbo.t6 32 Kb 18 Kb empdb.dbo.t8 32 Kb 18 Kb empdb.dbo.t7 32 Kb 20 Kb tempdb.dbo.tempcachedobjstats 16 Kb 8 Kb tempdb.dbo.tempobjstats 16 Kb 4 Kb --------- company_cache --------- Run Size : 1.00 Mb Usage% : 0.39 LR/sec : 0.07 PR/sec : 0.07 Hit%: 0.00 Cache Partitions: 1 Spinlock Contention%: 0.00 Buffer Pool Information ----------------------------------------------------------------------------- IO Size Wash Size Run Size APF% LR/sec PR/sec Hit% APF-Eff% Usage% ------- ---------- ----------- ------ -------- -------- ------ -------- ------ 2 Kb 204 Kb 1.00 Mb 10.00 0.07 0.07 0.00 n/a 0.39 Object Statistics ----------------------------------------------------------------------------- Object LR/sec PR/sec Hit% Obj_Cached% Cache_Occp% -------------------- ------- ------- ------ ----------- ----------- empdb.dbo.history 0.07 0.07 0.00 25.00 0.39 Object Obj Size Size in Cache -------------------- ----------- ------------- empdb.dbo.history 16 Kb 4 Kb ------------- companydb_cache ------------- Run Size : 5.00 Mb Usage% : 100.00 LR/sec : 380.97 PR/sec : 56.67 Hit%: 85.13 Cache Partitions: 1 Spinlock Contention%: 0.00 Buffer Pool Information ----------------------------------------------------------------------------- IO Size Wash Size Run Size APF% LR/sec PR/sec Hit% APF-Eff% Usage% ------- ---------- ----------- ------ -------- -------- ------ -------- ------ 2 Kb 1024 Kb 5.00 Mb 10.00 380.97 56.67 85.13 98.42 100.00 Object Statistics ----------------------------------------------------------------------------- Object LR/sec PR/sec Hit% Obj_Cached% Cache_Occp% ----------------------------- ------- ------- ------ ----------- ----------- company_db.dbo.emp_projects 41.07 22.80 44.48 19.64 9.45 company_db.dbo.dept_det 93.03 20.67 77.79 99.08 54.53 company_db.dbo.emp_perf 116.70 2.63 97.74 97.77 34.18 company_db.dbo.dept_locs 0.43 0.17 61.54 50.00 0.16 Object Obj Size Size in Cache ----------------------------- ----------- ------------- company_db.dbo.emp_projects 2464 Kb 484 Kb company_db.dbo.dept_det 2818 Kb 2792 Kb company_db.dbo.emp_perf 1790 Kb 1750 Kb company_db.dbo.dept_locs 16 Kb 8 Kb TUNING RECOMMENDATIONS -------------------------------------------------------------------- Usage% for 'default data cache' is low (< 5%) Usage% for 4k buffer pool in cache:default data cache is low (< 5%) Usage% for 2k buffer pool in cache:default data cache is low (< 5%) Usage% for 'company_cache' is low (< 5%) Usage% for 2k buffer pool in cache:company_cache is low (< 5%) Consider adding a large I/O pool for 'companydb_cache' LEGEND------ LR/sec - number of logical reads per second, i.e. sum of cache & disk readsPR/sec - number of physical reads per second i.e. disk readsRun Size - size of cache or buffer pool in KilobytesCache Partitions - number of cache partitionsSpinlock Contention% - Percentage spinlock contention for the cacheHit% - ratio of hits to total searchesUsage% - ratio of pages referenced to Run Size Wash Size - wash size of buffer pool in KilobytesAPF% - asynchronous prefetch % for this buffer poolAPF-Eff% - Ratio of buffers found in cache and brought in because of APF to the number of APF disk reads performed Object - combination of db, owner, object and index nameObj Size - size of the object in KilobytesSize in Cache - size occupied in cache in Kilobytes at the end of sampleObj_Cached% - Ratio of 'Size in Cache' to 'Obj Size'Cache_Occp% - Ratio of 'Size in Cache' to 'Run Size' of cache
