SQL Server Performance

Procedure Cache hit rate

Discussion in 'Third Party Tools' started by CanadaDBA, Feb 9, 2007.

  1. CanadaDBA New Member

    I have installed a SQL Server diagnose tool for evaluation. It prompts and warns me that "Procedure Cache hit rate is for example 15%. Its help indicates:

    The Procedure Cache Hit Rate alarm is raised when the ratio between the number of times SQL Server looks for a plan in the procedure cache and the number of times it does not find a required plan in the procedure cache falls below a threshold.

    A low procedure cache hit rate indicates that SQL Server is finding fewer of the query execution plans it needs already in memory and therefore has to perform more compiles. These extra compilations will degrade SQL Server performance by causing extra CPU load.

    What can I do to increase the rate?

    CanadaDBA
  2. satya Moderator

    What was that tool you have downloaded for evaluation?
    Is it a third party tool?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. bradmcgehee New Member

    Assuming you really have a cache problem, one of the easiest ways to correct it is to add more RAM to your server. But you would want to do some more research before you came to the conclusion that your server needs more RAM. For example, what is your buffer cache hit ratio? If it runs 99% and above most, if not all the time, then you probably have enough RAM already.

    -----------------------------
    Brad M. McGehee, SQL Server MVP
  4. CanadaDBA New Member

    The tool is Spotlight from Quest.

    The problem is "Procedure Cache hit rate" not "Buffer cache rate".

    CanadaDBA
  5. satya Moderator

    Ah, then better contact the vendor for further details and also review the Software spotlight section for that tool.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. bradmcgehee New Member

    The first question you should be asking yourself is if your server has a performance problem? If so, then lots of things can contribute to it. Again, check your Buffer Cache Hit Ratio. If it is 99% or higher, then the Procedure Hit Cache Ratio is really not important to examine. But if your Buffer Cache Hit Ratio is below 99%, then I would consider adding more RAM to the server.

    -----------------------------
    Brad M. McGehee, SQL Server MVP
  7. Pilaco New Member

    quote:Originally posted by bradmcgehee

    The first question you should be asking yourself is if your server has a performance problem? If so, then lots of things can contribute to it. Again, check your Buffer Cache Hit Ratio. If it is 99% or higher, then the Procedure Hit Cache Ratio is really not important to examine. But if your Buffer Cache Hit Ratio is below 99%, then I would consider adding more RAM to the server.

    -----------------------------
    Brad M. McGehee, SQL Server MVP

    Why about "check your Buffer Cache Hit Ratio. If it is 99% or higher, then the Procedure Hit Cache Ratio is really not important to examine." ?

    Procedure Hi Cache Ration means nothing in such situation?
    Then when it would be important?

    Our Buffer Cache Hit Ratio is above 99% but Procedure Cache Hit Ratio is only 40 somthing percent.

    Bother me!!

    JD
  8. Pilaco New Member

    quote:Originally posted by bradmcgehee

    The first question you should be asking yourself is if your server has a performance problem? If so, then lots of things can contribute to it. Again, check your Buffer Cache Hit Ratio. If it is 99% or higher, then the Procedure Hit Cache Ratio is really not important to examine. But if your Buffer Cache Hit Ratio is below 99%, then I would consider adding more RAM to the server.

    -----------------------------
    Brad M. McGehee, SQL Server MVP

    Why about "check your Buffer Cache Hit Ratio. If it is 99% or higher, then the Procedure Hit Cache Ratio is really not important to examine." ?

    Procedure Hi Cache Ration means nothing in such situation?
    Then when it would be important?

    Our Buffer Cache Hit Ratio is above 99% but Procedure Cache Hit Ratio is only 40 somthing percent.

    Bother me!!

    JD
  9. satya Moderator

    As a general recommendation the buffer cache hit ratio should be maintained in the range of 90% or higher. Be aware that every server has its own personality and might exhibit excellent performance with below average readings for the cache hit ratio. Also it is evident that excessive logical I/O activity can produce a very high cache hit ratio while actually degrading overall database performance, so a high buffer cache hit ratio is not the silver bullet for overall high performance in SQL Server.

    At the same time check the page life expectancy stats, as it estimates the page remain in buffer cach. Obviously, pages served from memory result in much shorter response times than pages that must be read from disk and then into the cache. So, it is wise for often used data to be pinned in the buffer cache.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page