Buffer Cache Ratio and scripts
-
Hello:
I'm having a problem reconciling the results of two scripts, using sysperfinfo the return the percentage of Buffer Cache Hit Ratio. MOst often they are close within a 1/2-1 percentage, but sometimes I get a huge discrepancy. If I run them in the same instance of sql query analyzer (so its at the same time), I can get 65% from one result, and 99% from the other. You will probably recognize the script and formulas below. I need to provide on-going accurate results. Can someone analyze these scripts and help me figure out why they (sometimes) yield different results? TIA!
SCRIPT#1
---------
select convert(decimal(15,2),(t1.cntr_value*1.0/t2.cntr_value*1.0 *100.0))
from master.dbo.sysperfinfo t1,
master.dbo.sysperfinfo t2
where t1.object_name = 'SQLServer:Buffer Manager'
and t2.object_name = 'SQLServer:Buffer Manager'
and t1.counter_name = 'Buffer Cache Hit Ratio'
and t2.counter_name = 'Buffer Cache Hit Ratio Base'
and t1.instance_name = t2.instance_name
and t2.cntr_value > 0
--------------------------------------------------------------------------
SCRIPT#2 this gets total buffer cache, used, free, and the percentage (this is what I need)
---------
DECLARE @total_memory_kb float,
@buffer_cache float,
@buffer_cache_free_kb float,
@free_pages float,
@buffer_cache_used float
select @total_memory_kb = cntr_value
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Memory Manager' and
counter_name = 'Total Server Memory (KB)'
select @free_pages=(isnull(cntr_value,0) * 8192)/1024--/1024
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Free Pages'
select @buffer_cache=(isnull(cntr_value,0) * 8192)/1024--/1024
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Total Pages'
select @buffer_cache_used=@buffer_cache-@free_pages
select bcu=(@buffer_cache-@free_pages)/1024,tbc=@buffer_cache/1024,fbc=@free_pages/1024,bc_pct=(@buffer_cache_used/@buffer_cache)*100
-
This was removed by the editor as SPAM
-
I guess I should just simply my question and ask, what is the best accurate way via tsql, to get the current BUffer Cach Hit Ratio? Hope someone can answer
TIA -
Depressing that no one has any ideas about this? Hasn't anyone been interested in their Buffer Cache Hit ratio?
-
-- Maybe this SQL will Help
-- From http://www.sqlmag.com/Article/ArticleID/26950/sql_server_26950.html
SELECT perf1.object_name, perf1.counter_name, perf1.instance_name,
'value' = CASE perf1.cntr_type
WHEN 537003008 -- This counter is expressed as a ratio and requires calculation.
THEN CONVERT(FLOAT,
perf1.cntr_value) /
(SELECT CASE perf2.cntr_value
WHEN 0 THEN 1
ELSE perf2.cntr_value
END
FROM master..sysperfinfo perf2
WHERE (perf1.counter_name + ' '
= SUBSTRING(perf2.counter_name,
1,
PATINDEX('% Base%', perf2.counter_name)))
AND (perf1.instance_name = perf2.instance_name)
AND (perf2.cntr_type = 1073939459))
ELSE perf1.cntr_value -- The values of the other counter types are
-- already calculated.
END
FROM master..sysperfinfo perf1
WHERE (perf1.cntr_type 1073939459) -- Don't display the divisors.
David Bird
-
If you check Technet you'll find an article on how to interpret the datra stored within sysperfinfo, which isn't stored quite how you might think it is!
http://support.microsoft.com/kb/555064
Yes I use sysperfinfo extensively, am I interested in the Buffer Cache Ratio, not really in as much as the limitations of memory on the boxes I normally look after are usually decided elsewhere or the version isn't enterprise - there are lots of reasons, mainly financial, that have more influence then my desire to have memory. That said most places I work have Idera SQL Diagnostic Manager so I have this info. Other than that I imagine most people use perfmon.
this query returns the cache hit ratio, which is possibly of more interest
select s1.instance_name,s1.cntr_value, (convert(real,s1.cntr_value)/convert(real,s2.cntr_value))*100
from sysperfinfo s1 join sysperfinfo s2 on s1.instance_name=s2.instance_name
where (s1.cntr_type=537003008 and s1.counter_name like 'cache hit%' and s1.cntr_value!=0) and (s2.cntr_type=1073939459 and s2.counter_name like 'cache hit%' and s2.cntr_value!=0)
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply