SQL Server Performance

Cache hit ratio and SQL cache memory

Discussion in 'Performance Tuning for DBAs' started by tdc, Jul 7, 2003.

  1. tdc New Member

    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
  2. rushmada New Member

    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
  3. tdc New Member

    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
  4. rushmada New Member

    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
  5. tdc New Member

    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
  6. satya Moderator

    Bob, is it a dedicated SQL server or any applications running parallely?
    How about settings for SQL Server memory, dynamic or defined?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. tdc New Member

    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)
  8. sqljunkie New Member

    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?
  9. Luis Martin Moderator

    Bob:

    Would you please tell us SQL version and SP?

    Thank

    Luis Martin
  10. tdc New Member

    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
  11. satya Moderator

    Bob, is it possible to turn off ANTI VIRUS and see any difference.
    Check whether any information from Director agent logs (if any available) about slow performance.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  12. Luis Martin Moderator

    Bob:

    I can't find where, using performance monitor, is "Average Cache Hit Ratio".

    I find Cache Hit Ratio for each objects like you show in last posted.

    Would you tell me where is this counter?

    Sorry if easy to find but I can't.

    Thank,

    Luis
  13. tdc New Member

    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
  14. satya Moderator

    Any information from the Management s/w log?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  15. Luis Martin Moderator

    Hi Bob,

    Thank you, I'm working now with SQL 7.0 SP3 and, I supose, this counter is not available.
    I'll check this with other client with 2000.

    Anyway, I dont remember any relevant document about Total Cache hit ratio.
    Yes, about Buffer Cache Hit as rushmada sed.
    If you fill poor performance try with SQL Trace and may be you will find new indexs or new statistics.

    Sorry again,

    Luis
  16. tdc New Member

    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
  17. sqljunkie New Member

    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...
  18. Luis Martin Moderator

    Bob:

    If I were you I`ll trace 2 or 3 critical hours with profiler. Look the trace and try to find longer SQLs.
    Cut a paste into Query Analizer and run Index Query Tuning to find what is goin on.

    Luis
  19. tdc New Member

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

    Bob

Share This Page