Buffer Cache Hit Ratio = 0

Question

Buffer Cache Hit Ratio = 0

skrilla99

SSCertifiable

Points: 5795
More actions
October 5, 2007 at 12:00 pm

#118466

Hey Everyone,
I have a single machine running SQL Server 2000 (8.00.2039) on Windows 2003 SP2.
I have been running system monitor to gather statistics and I can't seem to get a reading on SQLServer:Buffer Manager->Buffer Cache Hit Ratio. All other readings - at least those that I am checking - look fine. However, the buffer cache hit ratio just sits at 0, totally flat lined.
Ideas?
Thanks!
Sincerely,
Dan B

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How much memory do you have in your machine? What is the Min/Max memory set at in sp_configure? If you monitor Target and Total SQL Server memory in Perfmon, what does it show?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

skrilla99 SSCertifiable Points: 5795 More actions · Answer 2

Physical Memory - 1 GB

Min/Max Memory in SQL Server - 4/2,147,483,647

Target (Avg) - 461,903

Total (Avg) - 70,550

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 3

(possibly) stupid question. Users have run queries on this, right?

As soon as something is read into the buffer, I'd assume the next query should get some type of hit.

The other thing is that your counters might be messed up.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

try reading the counter from sysperfinfo table ( in master ) You'll need to check out a KB to find out how to use the figures as they're not quite as you might think!

It's unusal for a single counter not to work - if the other sql counters are fine then I'd expect this to be so.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

skrilla99 SSCertifiable Points: 5795 More actions · Answer 5

Thanks for the input.

Users are certainly hitting this server. When certain application routines are run, I can watch page life expectancy drop from the thousands to the tens. So, of course, I am getting complaints that these routines are slow. The PLE drop is expected, but I am really put off by the lack of data in the buffer cache hit ratio counter.

At first, I thought it might be due to the fact that this system was configured with the page file on a non-system drive. I added a small page file to the system drive, but did not notice a change.

I wonder if there are simple tests that can verify counter functionality.

Anyway, I'll poke around in sysperfinfo and see what I can see...

Thanks again!

Sincerely,

Dan B.

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 6

I assume you're reading from Performance Monitor. If not let us know.

I don't think I've ever seen this counter die, but as Colin mentioned, usually all the SQL counters will be working or they all won't.

Do other counters (CPU, Mem, etc.) work fine?

Let us know if you find something. This is an interesting issue.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

I never use the buffer cache hit ratio preferring to watch page life as an indicator of use, microsoft say no lower than 300 but I've got to be honest I prefer it to be a lot higher - but I suppose it depends upon how busy the server is and how much memory you have, most servers i work with have 16gb and upwards.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

skrilla99 SSCertifiable Points: 5795 More actions · Answer 8

I'm not sure that I am using the table properly, but I think it is sufficient for my needs to just repeatedly check the values in sysperfinfo. This is especially true due to the fact that I don't need to find anything other than a non-zero value. With that in mind, I ran the following query a dozen or so times.

select [object_name], [counter_name], [cntr_value]

from master.dbo.sysperfinfo

where counter_name = 'Buffer cache hit ratio'

or counter_name = 'Buffer cache hit ratio base'

Every single time, both counters returned 0.

Steve - I am running performance monitor. The other counters that I have checked all appear to be working properly. At the very least, they are reporting numbers that are variable in nature.

I'll keep looking for now.

Thanks!

Sincerely,

Dan B

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 9

Dan,

If you can get the funds, I'd call PSS. Sounds like something is very strange on your system.

skrilla99 SSCertifiable Points: 5795 More actions · Answer 10

Yeah... that's what I was afraid of... 🙂

I'll post back if there are any updates.

Thanks again.

Sincerely,

Dan B

Bob Fazio SSChampion Points: 10794 More actions · Answer 11

skrilla99 (10/5/2007)
Physical Memory - 1 GB
Min/Max Memory in SQL Server - 4/2,147,483,647
Target (Avg) - 461,903
Total (Avg) - 70,550

SQL Server and Windows 2003 with only 1GB of RAM?

There is your problem. I expect you really have a hit ratio of zero. I would at least up it to 2G.

skrilla99 SSCertifiable Points: 5795 More actions · Answer 12

I think that is a good point and certainly addresses the performance problem that brought this to light, but I find it hard to believe that page life expectancy can be in 4 and 5 digits while the buffer cache hit ratio is unwaveringly 0.

Sincerely,

Dan B.

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 13

1GB will run SQL. It all depends on load. I'd still expect a buffer cache ratio above 0, might be 10, but 0 doesn't make sense.