How is the buf_cachehit_ratio checkpoint calculation done by sqlserver probe

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

How is buf_cachehit_ratio checkpoint calculation done by sqlserver probe 

Environment

sql_server 5.10sql_server 5.2x

Resolution

Query executed for buf_cachehit_ratio checkpoint:

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