buf_cachehit_ratio formula

[Legacy User]

Hi,

Wonder if any one know exactly how sqlserver probe do buf_cachehit_ratio calculation? In CA documentation it does say

“The ratio is the interval number of cache hits divided by the interval number of cache look-ups”

 

But what it actually calculating? how does it actually getting the number? How can I prove to my DBA that CA is using the right formula to produce the right ratio value?

 

Hope I can get some help here. Let me know if need more detail. Thanks.

 

Gary

[Franklin Ravi D'souza]

Hi 

 

What Queries (updated ) are used by the sqlserver Probe? 

 

The above TEC has info on the queries used by sqlserver probe including buf_cachehit_ratio. This might provide you addtional info

 

Frank

[Legacy User]

Hi Frank,

Thanks for your quick reply. TEC refer to below statement:

{"buf_cachehit_ratio",

               "select a.cntr_value as page_lookups, b.cntr_value as page_reads, c.cntr_value as page_writes from master.sys.dm_os_performance_counters a, master.sys.dm_os_performance_counters b, master.sys.dm_os_performance_counters c where a.counter_name = 'Page lookups/sec' and b.counter_name = 'Page reads/sec' and c.counter_name = 'Page writes/sec' "

               " and LTRIM(RTRIM(a.object_name)) LIKE '%:Buffer Manager'  and LTRIM(RTRIM(b.object_name)) LIKE '%:Buffer Manager'  and LTRIM(RTRIM(c.object_name)) LIKE '%:Buffer Manager'"},

 

And return three values, 'page_lookups", "page_reads" and "page_writes".

 

Questions:

1. the value (%) is page_reads/page_lookups?

2. the value (%) is (page_reads+page_writes)/page_lookups?

 

Gary

[Franklin Ravi D'souza]

Hi Gary,

Query Executed :

select convert(dec(12,0),a.cntr_value) as page_lookups, convert(dec(12,0),b.cntr_value) as page_reads, convert(dec(12,0),c.cntr_value) as page_writes from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b, master.dbo.sysperfinfo c where a.counter_name = 'Page lookups/sec' and b.counter_name = 'Page reads/sec' and c.counter_name = 'Page writes/sec'

 

Variables :

lookups = Delta of current & last sample value of page_lookups column value

reads = Delta of current & last sample value of page_reads column value

writes = Delta of current & last sample value of page_writes column value

 

Formula :

if (lookups > 0) {

                hit = ((lookups - reads - writes) / lookups) *100;

                                }

else {

                hit = 100;

        }

 

This 'hit' value is sent as QOS and gets compared with the threshold

 

Frank

 

[Legacy User]

Hi Frank,

Thank you very much.

 

Gary