Cache Hit Ratio very low.

Cache Hit Ratio very low.

Post by Francis Mazeik » Fri, 27 Dec 2002 22:43:39



I have a Compaq running advanced server 2k, sql server 2k
enterprise edition sp2. 4 2ghz processors 6gb ram. 4.5gb
is dedicated to sql. awe is enabled. The cache hit ratio
is at 8%. there is a fair amount of reading from disk. how
do I get this setting above 90%. What setting do I need to
change.

Thanks.

 
 
 

Cache Hit Ratio very low.

Post by Kalen Delane » Fri, 27 Dec 2002 22:54:35


How much memory does task manager say that sqlserver.exe is using?
Do you have /PAE enabled or /3GB in the OS boot.ini?
How many concurrently users are there? Are they accessing similar data or
does everyone access different data?

Taking advantage of cache is only possible when you are re-reading data you
have already read before. If you are always going after new data, the size
of cache doesn't matter.

How long has SQL Server been running? Are you sure it's reached a steady
state?

Can you try running a large query and looking at the statistics IO? Then
rerun the same query and note the number of physical or read-ahead  IO's. If
you repeatedly get physical or read-ahead IO on the same query, then your
cache is not being effectively used.

--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


Quote:> I have a Compaq running advanced server 2k, sql server 2k
> enterprise edition sp2. 4 2ghz processors 6gb ram. 4.5gb
> is dedicated to sql. awe is enabled. The cache hit ratio
> is at 8%. there is a fair amount of reading from disk. how
> do I get this setting above 90%. What setting do I need to
> change.

> Thanks.


 
 
 

Cache Hit Ratio very low.

Post by Gert-Jan Stri » Sat, 28 Dec 2002 17:48:07


Maybe you have lots of recompiles. Use SQL Profiler or Performance
Monitor to monitor this.

Gert-Jan


> I have a Compaq running advanced server 2k, sql server 2k
> enterprise edition sp2. 4 2ghz processors 6gb ram. 4.5gb
> is dedicated to sql. awe is enabled. The cache hit ratio
> is at 8%. there is a fair amount of reading from disk. how
> do I get this setting above 90%. What setting do I need to
> change.

> Thanks.

 
 
 

1. Large DB buffer, low cache hit ratio

Hi, all,

The OS has 1G mem. SGA is about 700M, DB_BLOCK_BUFFERS is about 500M
(133230*4K) in bytes. The size of whole database data files is less
than 50G. So the DB_BLOCK_BUFFERS is more than 1% of the total database
size. While the database buffer cache hit ratio only 24%. Please advice
what happens here.

Thank you.

Helen

Sent via Deja.com
http://www.deja.com/

2. Problem with replication

3. Low hit ratio, but only for indexes

4. Null datetime from XML file?

5. Low lock hit ratio

6. Attatching database to new server without log files.

7. help - the cache hit ratio is constantly 0.0

8. Problem with StayOnTop and MessageBoxes

9. Why would cache hit ratio be over 100%

10. cache hit ratio!!!

11. cache hit ratio!!

12. buffer cache hit ratio drops to zero

13. Cache hit ratio '_Total' counter