Buffer Cache Hit Ration on a Reporting DB

Hi all.

First off, I'm a sys admin, not a DBA.

I'm trying to improve buffer cache hit ratio on a reporting database.
Database is approximately 130GB. Current server is a Proliant ML530 G2,
2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
day.

I've read over and over how BCHR needs to be above 90%, optimally
around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
system. On the reporting DB, it often drops to 80% or below.

My question is, should a reporting database have this large of a
difference in the hit ratio?

I'm working with our developer & DBA to improve the reports, but I also
need to make sure that the hardware is performing as good as it can.

I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
x64. We're waiting to make sure SP4 is completely kosher with our
application before upgrading.

Thoughts, ideas? Much appreciated!

-AJ

<ajohnson@echecktrac.com> wrote in message
news:1144442893.581038.299300@e56g2000cwe.googlegroups.com...
> Hi all.
>
> First off, I'm a sys admin, not a DBA.
>
> I'm trying to improve buffer cache hit ratio on a reporting database.
> Database is approximately 130GB. Current server is a Proliant ML530 G2,
> 2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
> raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
> day.
>
> I've read over and over how BCHR needs to be above 90%, optimally
> around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
> system.

Unfortunatly BCHR on OLTP databases is an irrelevant metric.  A high BCHR
often results from inefficient queries reading lots and lots of pages in the
cache.  And the memory size/database size ratio of an OLTP system often
doesn't even permit a low cache hit ratio.

>On the reporting DB, it often drops to 80% or below.
>
> My question is, should a reporting database have this large of a
> difference in the hit ratio?

Yes, it will quite likely be lower.  It's a matter of the ratio between the
cache (<8GB) and the data (130GB), and the predictablility of the queries
and acess paths.  Better indexing can help improve performance, although
since indexing reduces total IO, not just physical IO, you might not see a
better BCHR.

>
> I'm working with our developer & DBA to improve the reports, but I also
> need to make sure that the hardware is performing as good as it can.
>
> I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
> x64. We're waiting to make sure SP4 is completely kosher with our
> application before upgrading.
>

Going all the way to SQL 2005 64-bit will help alot more.  You can use all
that memory much more efficiently, plus it's easier to monitor the
performance and find the expensive queries.

David