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

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

Boots Performance

Hi ,
I already create clustered and non clustered index in order to improve the
reports performance. But after I test several times on my reports , the
processing time almost the same as before I index the table.
Any other method to increase the performance ? Can I index the "view" I
created ?
Travis Tan
Travis,
The group will need substantially more information in order to help diagnose
your performance issue. The basic answers to your questions are Yes. and
most likely yes depending on the version you are running.
Please post more information including:
SQL Server version
Basic hardware configuration
The query
The estimated query plan
What is the database used for (OLTP, DSS, Reporting, etc.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:5A01556F-70F4-4CC4-9779-2CAB8D4710F4@.microsoft.com...
> Hi ,
> I already create clustered and non clustered index in order to improve
> the
> reports performance. But after I test several times on my reports , the
> processing time almost the same as before I index the table.
> Any other method to increase the performance ? Can I index the "view" I
> created ?
>
> --
> Travis Tan

Boots Performance

Hi ,
I already create clustered and non clustered index in order to improve the
reports performance. But after I test several times on my reports , the
processing time almost the same as before I index the table.
Any other method to increase the performance ? Can I index the "view" I
created ?
Travis TanTravis,
The group will need substantially more information in order to help diagnose
your performance issue. The basic answers to your questions are Yes. and
most likely yes depending on the version you are running.
Please post more information including:
SQL Server version
Basic hardware configuration
The query
The estimated query plan
What is the database used for (OLTP, DSS, Reporting, etc.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:5A01556F-70F4-4CC4-9779-2CAB8D4710F4@.microsoft.com...
> Hi ,
> I already create clustered and non clustered index in order to improve
> the
> reports performance. But after I test several times on my reports , the
> processing time almost the same as before I index the table.
> Any other method to increase the performance ? Can I index the "view" I
> created ?
>
> --
> Travis Tan