Log Cache Hit Ratio + Buffer Cache Hit Ratio

Thanks in advance for any info.
SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
RAID 5.
What does one do when the Buffer Cache is at (and stays at) 99% and the Log
Cache is at 99%? Faster processors, more memory, change the RAID config?
Note: this is not daily activity on the box, but a monthly process that has
gone from 18 hours to 28 hours {and counting} with no changes.
Thanks,
MorganIf you're talking about the hit ratios, those are both
excellent numbers. Anything over 95% is optimal. When
those numbers start to drop, in general more memory is
the key, but be sure to check into it more closely before
purchasing hardware.
>--Original Message--
>Thanks in advance for any info.
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I
think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays
at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change
the RAID config?
>Note: this is not daily activity on the box, but a
monthly process that has
>gone from 18 hours to 28 hours {and counting} with no
changes.
>
>Thanks,
>Morgan
>
>.
>|||Morgan,
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
99% cache hit ratios are optimal.
This means that the server is practically always getting data from the
caches, rather than going to the disk subsystem.
It sounds more like the work needs to be done in the monthly process, as far
as optimizing it goes. Your hardware seems to be performing optimally.
When did it go from 18 to 28 hours? Suddenly? Over time? I would start
suspecting one of the following:
That perhaps either:
A.) The data set has grown
B.) Someone changed one of the queries
C.) Someone dropped a key index
D.) One or more of the above. :-)
James Hokes|||In addition to the other comments the log can be a bottle neck if on a Raid
5 and especially with the data. The Log cache ration doesn't mean much for
writes so you may want to check your disk queues on the Raid that houses the
log file anyway and make sure it has no issues. TempDB can be another place
to look. Usually month end type process does a lot of activity that uses
temp tables and you can have bottlenecks there as well. Essentially you
need to monitor the cpu and disk counters while this process is happening to
see where the bottleneck is coming from. If the data cache ratio is 99%
chances are more memory won't help much. If your procedures are optimized
then it will boil down to disk or cpu. But how sure are you that they are
that optimized?
--
Andrew J. Kelly
SQL Server MVP
"Morgan" <mfears@.spamcop.net> wrote in message
news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> Thanks in advance for any info.
> SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
RAM.
> RAID 5.
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
>
> Thanks,
> Morgan
>|||On Fri, 26 Dec 2003 13:42:08 -0500, "Morgan" wrote:
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change the RAID config?
>Note: this is not daily activity on the box, but a monthly process that has
>gone from 18 hours to 28 hours {and counting} with no changes.
In addition to what James, James and Andrew said, look at your
statistics. As your database grows over time, the distribution of values
across columns can change substantially. Out of date stats can
contribute to bad query plans, leading to long runs.
Are you able to pinpoint which part(s) of your monthly process chews the
most time?
cheers,
Ross
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello|||Thanks to everyone for their insight.
I was somewhat under the impression that if they're both max'd out, then
it's time to start looking at HW. I know for a fact the procedures need
"help", but it's an inherited process that for the first time this month,
took much longer than usual, which is why I knee-jerked my post. To the best
of my knowledge, none of the underlying objects has changed, but upon
further review (below), several tables are missing indicies where they are
required. I also saw quite a bit of parallelism happening via SP_WHO, and
have begun to question the multi-proc machine, which for better or worse, is
most likely fine.
I ran Profiler off-and-on (didn't want to make it any worse than it was),
and found a single procedure that appeared to be somewhat of a bottleneck,
lots of extensive reads for a single query, minimal (single, actually)
writes. Fortunately, a predecessor had the forethought to not use temp
tables, however, the tables in use are not properly indexed. Upon review of
the primary table in the afore mentioned procedure, it turns out there are
no indexes or unique constraints; along with a truncate table statement
against the table at the start of the procedure. I know the cost of a
properly placed index will fix the amount of reads against the suspect table
I saw in Profiler. Top all this off with cursors galore (note: not the
author, but the maintainer), and you can easily appreciate the mess I'm
dealing with. I'm reading this as the beginning of the end for the current
processes... ;)
Thanks again,
Morgan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOYObWMzDHA.2408@.tk2msftngp13.phx.gbl...
> In addition to the other comments the log can be a bottle neck if on a
Raid
> 5 and especially with the data. The Log cache ration doesn't mean much for
> writes so you may want to check your disk queues on the Raid that houses
the
> log file anyway and make sure it has no issues. TempDB can be another
place
> to look. Usually month end type process does a lot of activity that uses
> temp tables and you can have bottlenecks there as well. Essentially you
> need to monitor the cpu and disk counters while this process is happening
to
> see where the bottleneck is coming from. If the data cache ratio is 99%
> chances are more memory won't help much. If your procedures are optimized
> then it will boil down to disk or cpu. But how sure are you that they are
> that optimized?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Morgan" <mfears@.spamcop.net> wrote in message
> news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> > Thanks in advance for any info.
> >
> > SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
> RAM.
> > RAID 5.
> >
> > What does one do when the Buffer Cache is at (and stays at) 99% and the
> Log
> > Cache is at 99%? Faster processors, more memory, change the RAID config?
> > Note: this is not daily activity on the box, but a monthly process that
> has
> > gone from 18 hours to 28 hours {and counting} with no changes.
> >
> >
> >
> > Thanks,
> > Morgan
> >
> >
>