Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-11-18 : 10:14:04
|
| [code]select *from master..sysperfinfowhere counter_name like 'Buffer%'[/code]I am trying to get the Buffer Cache Hit Ratio on both an SQL Server 2000 and 2005 Server. ie. number of times page is in memory rather than going to disk. Something doesn't seem right. Both the ratio and ratio base seem to just bob up and down between 1 and 1000 - is it resetting itself. Also, when I clear the buffer cache (DBCC DROPCLEANBUFFERS) the ratio remains around 99% when I am doing a query over a huge table. Which doesn't make sense since it seems the SQL server is filling up its Data Pages at this time - Free Pages start around 160,000 and go down to around 1,000.Is something not working in terms of SQL Server reporting the hit ratio and ratio base for the Buffer Cache? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-18 : 15:39:10
|
| I would use perfmon to gather this data.-Chad |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-11-25 : 16:05:45
|
quote:
Originally posted by chadmat
I would use perfmon to gather this data.-Chad
Thanks for the idea. I get the same thing with PerfMon. Even when I clear the cache, the Buffer Cache Hit Ratio just says around 99/100%, which makes no sense. It really seamms like the Buffer Cache Hit Ratio readings are not working and I can't understand why? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-12-02 : 16:15:38
|
| I tried just now again.I get Buffer cache hit ratio = 21487 Buffer cache hit ratio base = 21487 The I re-run it a second later and I get Buffer cache hit ratio = 8Buffer cache hit ratio base = 8It just seems to jump around a lot, up and down.Is that not sounding like we have some problem on our machine with this? Has anyone else had a problem with this? Is there way of re-seting or be-building these counters? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-12-02 : 17:21:50
|
| Buffer Cache Hit Ratio is one of the more useless Perfmon statistics. I rarely see a server where it’s less that 97%.I pay more attention to Page Life Expectancy, Page Reads/sec, and Page Write/sec to get a better idea of how well SQL Server is doing with caching data.CODO ERGO SUM |
|
|
|
|
|
|
Buffer Cache Hit Ratio not working