SQL Server Performance

log cache hit ratio is less..help me

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by saikrishna, Apr 9, 2007.

  1. saikrishna New Member

    Hi, I am a Oracle DBA..recently started supporting SQL Server Databases..
    Started to learn the basics of sql server.
    I have few performance problems..
    I notice that the log cache hit ratio Counter is only 50%..

    1) What has to be done for this?

    2) IF i have to increase the log cache, how can i do that?

    3) Is it possible to increase the log cache alone or it is internally allocated from the entire memory.


    Answers, comments and suggestions are most welcome

    Thanks,
    sai.
  2. MohammedU New Member

  3. Luis Martin Moderator

    Log cache hit?.

    May be:

    Buffer cache hit ratio.
    Procedure cache hit ratio.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  4. saikrishna New Member

    Hi,

    The counter is log cache hit rato only.
    give below for your reference.

    Log Cache Hit Ratio - Percentage of log cache reads satisfied from the log cache.

    You can find it in MSSQL Server online books.
    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a7f9e7d4-fff4-4c72-8b3e-3f18dffc8919.htm


    Cheers,
    sai.
  5. Luis Martin Moderator

    Ok, I found it.

    My system show 80%.

    What % do you have?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  6. saikrishna New Member

    Hi,

    My system shows only 50.12%..

    Thanks,
    sai.
  7. Luis Martin Moderator

  8. joechang New Member

    the whole idea of approaching performance counters as having "acceptable" ranges is totally worthless and amount to an excercise is chasing meaningless specs

    kind of like thd distortion in stereo equipment,
    its possible to make this really really low,
    but your stereo does not sound any better


    think about it
    what should log cache read hits be?

    1. if you do not backup your log or rollback transactions,
    who cares? because you never read from log,
    the hit ratio will be based on one or 2 reads,

    2. if you do backup your logs,
    then its quite likely your log write was written to disk long ago,
    so of course your read will miss cache,
    the most likely case of a high hit rate i can guess at is that
    you have tx rollbacks,
    in which case the log write is still in cache,
    and now you have to roll back, ie, read from cache


    just focus on counters that actually useful meaning
  9. saikrishna New Member

    Hi,

    Thanks for the link,

    IF you could let me know the specific solution to improve the log cache hit ratio..that would be really helpful.

    Thanks,
    Sai.
  10. Luis Martin Moderator

    I suppose you post same time than Joe's post.
    Did you read it?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  11. saikrishna New Member

    Hi,

    Yes, I read it, I understand that completely..,
    Log cache hit ratio is depicting whether the rollback transactions can find the data in cache or log file..

    In that case this counter is not at all useful in terms of performance tuning.

    Pls correct me if i am wrong.

    cheers,
    sai.
  12. Luis Martin Moderator

    That is right.
    Most important is other counter to take care. You can find those counters in the link I already post.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  13. saikrishna New Member

    Hi,

    Could anyone explain me the cycle of Transaction Log file.

    I came to know that it takes care of redo log as well as Transaction Rollback in SQL Server.

    Once you fire a Transaction and commit it enters into the log cache and after it is flushed it goes into transaction log.,

    Pls explain me the process of truncations, archival, growth, shrink .

    Thanks,
    Sai.
  14. satya Moderator

  15. MohammedU New Member

    Good to know about Log Cache Hit Ratio
    I never used it... I don't think this counter is useful in any way to troubleshoot sql server issues...

    I don't think there is any way to control the "Log Cache Hit Ratio"....
    What is the "buffer cache hit ratio" ?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  16. Luis Martin Moderator

    Buffer cache hit ratio:

    Percentage of pages found in the buffer 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 an instance of SQL Server was started. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  17. MohammedU New Member

    Thanks Luis....

    I was asking the question to saikrishna about what is the "Buffer cache hit ratio" value not the defination...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  18. Luis Martin Moderator

    quote:Originally posted by MohammedU

    Thanks Luis....

    I was asking the question to saikrishna about what is the "Buffer cache hit ratio" value not the defination...





    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided �AS IS� with no warranties for accuracy.


    [:I][:I][:I]

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page