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

Browser Role get Admin Tools... why?

Hi there,
We're trying to set up a Browser Role on a Server for Reporting Services. We
have created the NT User, and then added a Browser role for them in RS. But
for some reason, even though when we access Reports and type in the Browser's
Credentials, all of the Admin Tools are still there and usable.
I've done the exact same thing previously and had no complications like
this. What could be causing this to happen?
Thanks
AndreThe Answer is...
the report web apps in IIS has anom access selected!
"Arkiliknam" wrote:
> Hi there,
> We're trying to set up a Browser Role on a Server for Reporting Services. We
> have created the NT User, and then added a Browser role for them in RS. But
> for some reason, even though when we access Reports and type in the Browser's
> Credentials, all of the Admin Tools are still there and usable.
> I've done the exact same thing previously and had no complications like
> this. What could be causing this to happen?
> Thanks
> Andre

Breaking a SQL Cluster

Is there a way to break my Active/Active cluster back to have a single
stand-alone SQL server?
internal politics and a rogue admin who applies security patches willie
nillie to the cluster nodes is causing more downtime than the cluster is
worth!
Hopefully there is a way that this can be done fairly painlessly.
Thanks in advance,
Ron G
You can uninstall a virtual server by running the installation wizard again.
The books online have the exact steps listed.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Ron Griffin" <rgriffin@.lattestonetech.com> wrote in message
news:8A9Zd.19191$YD4.2485@.newssvr12.news.prodigy.c om...
> Is there a way to break my Active/Active cluster back to have a single
> stand-alone SQL server?
> internal politics and a rogue admin who applies security patches willie
> nillie to the cluster nodes is causing more downtime than the cluster is
> worth!
> Hopefully there is a way that this can be done fairly painlessly.
> Thanks in advance,
> Ron G
>