Cache hit ratio and SQL cache memory

Our SQL server has 2 GB of RAM and is very lightly loaded. Performance monitor shows that our cache hit ratio averages about 75% and yet the SQL Cache memory is only 2.5 MB
I would have thought that SQL Server would use increase the cache size as needed



Bob Harris
Network Administrator
Tauranga District Council
New Zealand

The Buffer Cache Hit Ratio counter is application specific; however, a rate of 90 percent or higher is desirable. value is consistently greater than 90 percent, indicating that more than 90 percent of all requests for data were satisfied from the data cache.

In the task manager u see is there any service/any application is running. If not
u try restarting all sql server services in the server and restart and test.

Thanks



Rushendra

Hi Rushendra,

I was looking at the Cache Hit Ratio, not the Buffer Cache hit Ratio.
It's evening here now so the server is almost idle. The Cache Hit Ratio has risen to 80%
I've started monitoring the Buffer Cache Hit Ratio and it is currently at 99.8%, but I guess this will drop during the day.

Thanks
Bob

Bob,

Pardon for my ignorance, Yes as u said cache hit ratio will be handled by the sql server as needed

if it is cache hit ratio then

Percentage of pages found in the cache without having to read from disk.
The ratio is the total number of cache hits divided by the total number of cache lookups since SQL Server was started.
After a long period of time, the ratio moves very little.
Because reading from the cache is less expensive than reading from disk, you want the ratio to be high. The higher this value is, the better. Generally, you can increase the cache hit ratio by increasing the amount of memory available to SQL Server.

For more info ref sql server books online topic is - bottleneck - cache manager object.



Rushendra

Hi Rushendra,

I agree with what you have said but still don't beleive that the serve needs more memory. The server is lightly loaded - less than 20 transactions per second. Actually, as we run SQL Server standard I can't add more anyway as we already have 2 GB.
What I can't understand is why the counter SQLServer:Memory ManagerSQL Cache Memory shows that the cache size is only 2.5 to 3 MB. Why doesn't it autpmatically take more RAM to improve the cache hit ratio?

Bob

It is a dedicated SQL server. Memory is dynamically configured (min = 0MB, max = 2303MB)
Reserve physical memory for SQL Server = true
Available Physical Memory (from performance monitor) = 350MB
The only other applications running are McAfee antivirus and IBM Director agent (server management)

Bob,

Interesting topic...

Is the counter you're talking about is the SQLServer:Cache Manager object, Total instance, Cache Hit Ratio? If so I am curious what the cache hit ratios are for the other instances within this object are.

A potential theory to your issue could be that during the day there are a lot of different adhoc queries being submitted. Because of this the cache hit ratio for that particular instance is low, skewing the Total instance.

So why wouldn't SQL Server allocate more memory to the Adhoc cache? Well I'm not an expert on SQL Server caching, but I think it makes more sense to keep data in the procedure buffer cache, instead of reading from disk, than keeping random adhoc query plans cached. The penalty for reading from disk is probably higher than compiling an adhoc query.

Could you post the cache hit ratios for the other instances?

Hi,

The machine is running SQL Server 2000 SP3
It was the total figure that I provided.
The individual counters are as follows:

Adhoc SQL Plans 99%
Cursors 41%
Execution Contexts 66%
Misc Normalized Trees 85%
Prepared SQL Plans 91%
Procedure Plans 94%
Replication Procedure Plans 0%
Trigger Plans 97%

Bob

Bob Harris
Network Administrator
Tauranga District Council
New Zealand

Hi Luis,

The counter I'm looking at it is SQLServer:Cache Manager - Cache Hit Ratio - _Total
Although it is referred to as a total I presume it is really an average figure.

By the way, I stopped the antivirus software but this had no effecr on the cache hit ratio or the SQL cache memory. It did however significantly lower the number of memory page faults/second.

Bob

IBM Director isn't indicating any problems.
I should mention that the server isn't actually performing badly. It was purchased a few months ago in anticipation of a new major project. Up till now it has been running a few small databases and I only noticed the low cache hit ratio while benchmarking the server before adding the new database. All the other counters indicate that the server is almost idle. The 3 things that don't add up are:
Cache hit ratio 75%
SQL cache memory 3.5MB
Available memory 380MB
If I was the SQL Server I would use some of the available memory to increase the cache size.

Bob

Does the applciation make heavy use of cursors? Seeing that the cursor cache hit ratio is so low perhaps you want to look at modifying the "Cursor Threshold" sp_configure parameter. Check out:

http://www.sql-server-performance.com/sql_server_configuration_settings.asp

I'd also take a look at the Books Online topic "Execution Plan Caching and Reuse" for more information on why the Execution Context ration is not higher.

Beyond the fact that you are not experiencing a performance problems, I would not expect these ratios to be any higher...

Thanks, I'll try running the profiler to see what I can find.

Bob